Thread: check constraints

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.
Thanks.
Jodi Kanter

On Wed, 2004-08-04 at 15:50, Jodi Kanter wrote:
> 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                                          olly@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