pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcheck constraints
2+ messages / 2 participants
[nested] [flat]
* check constraints
@ 2004-08-04 14:50 Jodi Kanter <jkanter@virginia.edu>
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 15:37 Oliver Elphick <olly@lfix.co.uk>
parent: Jodi Kanter <jkanter@virginia.edu>
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