pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
check constraints
2+ messages / 2 participants
[nested] [flat]

* check constraints
@ 2004-08-04 14:50 Jodi Kanter <jkanter@virginia.edu>
  2004-08-04 15:37 ` Re: check constraints Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 1 reply; 2+ messages in thread

From: Jodi Kanter @ 2004-08-04 14:50 UTC (permalink / raw)
  To: pgsql-admin

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
-- 

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu <mailto:jkanter@virginia.edu100>

 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: check constraints
  2004-08-04 14:50 check constraints Jodi Kanter <jkanter@virginia.edu>
@ 2004-08-04 15:37 ` Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 0 replies; 2+ messages in thread

From: Oliver Elphick @ 2004-08-04 15:37 UTC (permalink / raw)
  To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-admin

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 




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-08-04 15:37 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-04 14:50 check constraints Jodi Kanter <jkanter@virginia.edu>
2004-08-04 15:37 ` Oliver Elphick <olly@lfix.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox