Forbid to add rows with a specific foreign key in SQL (Postgres)

Question

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?

Belayer · Accepted Answer · 2022-09-08 23:39:02Z

1

You can get what you looking for by:

Add a boolean column to sets table indicating that the set accepts additional referencing items or not. Default value to True (will accept items).
Create an insert trigger on items that checks the related sets If the set does not accept additional items then raise an exception.
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).

answered Sep 8, 2022 at 23:39

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Igor Loskutov Over a year ago

Thank you for this detailed answer. It helped me a lot. For my case, I think it'll be "good enough" to implement that because we don't allow omissions to "sets" by design (it's a ledger). Any omissions facts are new entries and are first-class citizens in the system.

Igor Loskutov Over a year ago

Actually, it's also going to be flag = false by default (and = true at the end), seems to convey the intent better in my case (closed=true) vs (opened=false)

Collectives™ on Stack Overflow

Forbid to add rows with a specific foreign key in SQL (Postgres)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related