Define one for many relationships with SQL

Question

I am looking for a way to establish the relationship between one and two between two tables. The table structures are described below, but I tried to leave everything that has nothing to do with the problem.

The objects table has 1 column named uuid .

The contents table has 3 columns called content , object_uuid and timestamp .

The main idea is to insert a string in objects and get a new uuid from the database. This uuid then used for each line in contents to bind content to objects.

Now I am trying to use a database to provide this:

• Each line in contents refers to a line in objects (a foreign key must be executed)
• No row in objects exists, at least row in contents

These restrictions must be observed when making transactions.

Ordinary triggers cannot help, because when a row is written in the objects table, there cannot be a row in contents . Postgres has so-called constraint triggers , which can be delayed until the end of the transaction. You could use them, but they seem to be a kind of internal structure, not intended for everyday use.

Ideas or solutions should be standard SQL (preferred) or work with Postgres (version doesn't matter). Thanks for any input.

Answer 1

The main problem is that in addition to foreign key restrictions; no restriction can refer to another table.

It’s best to denormalize this a bit and have an object column containing a contents counter that references it. You can create a trigger to update it.

contents_count INTEGER NOT NULL DEFAULT 0

It will not be as unbreakable unless you put some user protection on who can update this column. But if you keep him informed about the trigger and all you want to avoid is accidental damage, that should be enough.

EDIT . According to the comment, CHECK restrictions are not delayed. This solution will cause an error if all the content is deleted, even if the intention is to add more to the same transaction.

Answer 2

Perhaps what you want to do is normalize a bit more. You need a third table that references elements from other tables. The objects table must have its own uuid, and the contents sholud table also has its own uuid and does not refer to the objects table. The third table should only contain links to two other tables, but the primary key is a combination of both links. for example, you have a uuid of the objects table, and you want the entire contents of this uuid, assuming that the third table has the object_uuid and content_uuid columns, and the table contents has its own sequential column named uuid, your query should be as follows:

 SELECT * FROM thirdtable,contents WHERE thirdtable.content_uuid = contents.uuid AND thirdtable.object_uuid=34; 

Then you can use the inclusion trigger in each table

 CREATE TRIGGER my_insert_trigger AFTER INSERT OR UPDATE ON contents FOR EACH ROW EXECUTE PROCEDURE my_check_function(); 

and then in the my_check_function () function, delete each row in objects that is not in the third table. Someone else answered first when I answered, if you liked my solution, I could help you make my_check_function () function.