Обсуждение: 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:

I am trying to copy two delimited text files into a database using the COPY
command. The first file - with about 280,000 lines - takes about 5-10
minutes to copy. The second file - with about 89,000 lines - is still
running after 4 days!
This second table has refrential integrity constraints on 3 fields (it's a
15 field table)

I have also copied this file into a table with no refential integrity
constraints and then used an INSERT command to copy the records into the
correct table. This is also taking days.

So, if I can't copy this file into a table and then apply the referential
integrity constraints, can anybody help me work out what my options are?

Thanks

Adam Fisher

On Fri, Dec 07, 2001 at 11:00:22AM +1100, Adam Fisher wrote:
> My question is, is it possible to create a referential integrity constraint
> on an exisiting, already populated, table??

Yes, cf:

ALTER TABLE table
    ADD table constraint definition

eg:

alter table "tblAddress"
  add constraint addfk
  foreign key("PersonID") references "tblPerson"("ID") match full;

Cheers,

Patrick

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