check constraints

Oliver Elphick

2004-08-04 15:37:20 UTC

Post by Jodi Kanter
I have little experience with postgres' check constraint features and
was hoping to get some guidance. We have a table which houses links to
various user files. The PK of this table is fi_pk. There are several
tables in which a foreign key (fi_fk) to this field exist. We are
implementing a feature which will allow users to remove old files from
their repositories. It would be nice to have a check that goes out to
all tables that have this fi_fk field and checks to see if the fi_pk
for their particular record exists and would then not allow the
deletion if a link occurs somewhere.
I realize that I can do this with specific SQL in the code but was
wondering if it's possible to implement a sort of general database
wide check of this nature. That way if future tables are added with
this FK field then the code would not have to be altered.

You do it when you define the foreign key in the other tables:

CREATE other_table (
...,
fi_fk ... REFERENCES table (fi_pk)
ON UPDATE CASCADE
ON DELETE RESTRICT,
...
);

This says that if the foreign key field changes its value in table the
corresponding value in other_table should be changed too. If an attempt
is made to delete the value in table when it is referenced by any rows
in other_table, the deletion will not be permnitted.
--
Oliver Elphick ***@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"And not only so, but we glory in tribulations also;
knowing that tribulation worketh patience; And
patience, experience; and experience, hope."
Romans 5:3,4

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)