I have a table with a boolean column automated. When that field is set to TRUE, then another table needs to have an entry referring to that row.
Table A
id | automated --------------------- 1 | False 2 | True 3 | False Table B
id | FK-TableA | Value ------------------------------- 2 | 2 | X So whenever a new entry gets inserted into Table A where automated is set to TRUE, then there also has to be a row inserted (or present) in Table B with a referece to it.
It seems an unnatural flow to me, with the constraint you are stating the natural flow should be creating a TRIGGER on Table B that inserts a record on Table A whenever a new Table B record is inserted.
But I understand this is a simplification of a more elaborated problem so if you really need to create this kind of procedure, there is still a question to be answered, what happens when check is negative, should there be an exception? should the record be inserted with FALSE instead of TRUE, should the record need to be ignored? there are two options from my point of view:
TRIGGER before INSERT on table A that updates the table accordingly (Create a PROCEDURE that checks if this exists and a TRIGGER that executes this procedure)RULE on insert on your table A that checks that record exists on Table B and changes record or does instead nothing.With a little more background I can help you with Trigger/Rule.
Anyway, take into account this can be performance-wise a real mistake if this table gets lots of INSERTs and you should go for some offline(as not being done on the live INSERT) procedure instead of doing on live INSERT
It is ugly and introduces a redundancy into the database, but I cannot think of a better way than this:
Introduce a new column b_id to a.
Add a UNIQUE constraint on ("FK-TableA", id) to b.
Add a foreign key on a so that (id, b_id) REFERENCES b("FK-TableA", id).
Add a CHECK (b_id IS NOT NULL OR NOT automated) constraint on a.
Then you have to point b_id to one of the rows in b that points back to this a row.
To make it perfect, you'd have to add triggers that guarantee that after each modification, the two foreign keys are still consistent.
I told you it was ugly!
