I have two append-only tables, one of which has a foreign key to another.

For the purpose of this question, I'll call them sets and items.

sets has an unique id.

items has a unique id and a foreign key to sets.id.

How I work with these tables in code is that in a transaction, I create a sets row and several items rows that reference this sets row.

After this initial operation, I'd like to make it impossible to write any more items rows that would refer to that sets row, preferably on the database level with triggers, constraints, or other means to achieve that. In that sense, the sets row is closed for accepting any more items.

Is there a way to achieve what I envision here with Postgres?

And secondly, is this even a good practice to do that on database level?

CodePudding user response：

You can get what you looking for by:

1. Add a boolean column to sets table indicating that the set accepts additional referencing items or not. Default value to True (will accept items).
2. Create an insert trigger on items that checks the related sets If the set does not accept additional items then raise an exception.
3. Create an update trigger on sets that does not allow changing the column in #1 above from false to true.

Now create a process which inserts into sets with flag = true (default). Then insert the related items. Finally set the flag = false. This creates the sets and items tables but will not allow additional related items for the set. It still leaves the ability to add items to a specific set until fie flag is set. (see demo).

So is this even a good practice? Basically that is unanswerable without the specifics of how and when it will be used. (IMHO) Generally, for an original entry transactional system, it is a poor practice. Errors and omissions happen all the time, this process does not allow for them. It requires perfection that does not happen in the real world. On the held for an system generated archive it probably is a good practice (may not even be restrictive enough).