Re: referential integrity on existing table - Mailing list pgsql-general

> 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@incyte.com
                       *** Incite genomics! ***