Re: referential integrity on existing table

> Hi Everyone
>
> My question is, is it possible to create a referential integrity constraint
> on an exisiting, already populated, table??
> My reasons for this are:

How about

ALTER TABLE tablename ADD CONSTRAINT constraint_name FOREIGN KEY
(childfield) REFERENCES parenttable (parentfield);

One downside: If anything goes wrong (some record violates referential
integrity), the command will fail without stating what record caused
the failure. So you might try something like the following first

SELECT * FROM childtable WHERE NOT EXISTS
(SELECT * FROM parenttable WHERE
childtable.keyfield = parenttable.keyfield);

to locate potentially offending records.

Paul

>
> Adam Fisher

Paul B. Laub http://astatine.incyte.com/laub (650) 845-5411 (voice)
Incyte Genomics, Inc. 3160 Porter Dr. Palo Alto, CA 94304 plaub(at)incyte(dot)com
*** Incite genomics! ***