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! ***
Import Notes
Resolved by subject fallback