Managing Foreign Keys under SQL Server

Question

Managing Foreign Keys under SQL Server

howler

Grasshopper

Points: 11
More actions
April 20, 2006 at 9:50 am

#112767

Please let me know the best way to manage foreign keys. I have a sql 2000 db with a many FKs and I am having trouble when I dts the data from dev to prd systems. Frequently we get FK violations and the dts fails to copy tables. What I have had to do as a temp fix is take off the FKs in the diagram on our prd system then move the data, then re-apply the keys.
Can someone send me ideas or best practices to achieve this in the future without having to do this manual process?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

I automate this very functionality by writing my foreign keys as drop and create statements to a FK table, use the same dts packages to move the FK table to the destination, and apply the foreign key statments from the table to the destination table as the last step in my dts...etc...

Pam Brisjar SSChampion Points: 12094 More actions · Answer 2

What kind of errors are you getting?

Guessing, I'd say it's your order of insert / update which is at fault. Examine the order to ensure you aren't inserting data for a foreign key which doesn't yet exist.

i.e.

Table1

PK

Table2

PK

FK_Table1

Table3

PK

FK_Table1

FK_Table2

You would insert to Table1, Table2 then Table 3. Any other way and you'd get errors.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

I do what Scorpian does. I use this script to create the drop and add statements for the FK constraints:

/* Generates script to drop foreign key constraints. If you plan on recreating them,

then be sure to run the re-create script below, FIRST, while the foreign key

constraints are still in place

*/

select 'ALTER TABLE ' + cast(table_name as char(40)) + 'DROP CONSTRAINT '

+ cast(constraint_name as char(50)) + char(13)

from information_schema.constraint_table_usage

where constraint_name like 'FK%'

order by table_name, constraint_name

/* Generates script for recreating foreign key constraints. Run BEFORE running

DROP statements, that is, while the foreign key constraints still exist.

WARNING: this script does not pickup the ON UPDATE/DELETE CASCADE clause, if present.

These clauses could be important for the functioning of a database!

*/

select 'ALTER TABLE ' + cast(t.name as char(40)) + 'ADD CONSTRAINT '

+ cast(c.name as char(50)) + 'FOREIGN KEY ' + '(' + fc.name + ') ' + 'REFERENCES '

+ cast(rt.name as char(30)) + '(' + rc.name + ') ' + char(13)

from (select distinct constid, fkeyid, rkeyid, fkey, rkey from sysforeignkeys) as FK

join sysobjects c on c.id = FK.constid

join sysobjects t on t.id = FK.fkeyid

join sysobjects rt on rt.id = FK.rkeyid

join syscolumns fc on fc.colid = FK.fkey and fc.id = FK.fkeyid

join syscolumns rc on rc.colid = FK.rkey and rc.id = FK.rkeyid

Greg

Lowell SSC Guru Points: 323518 More actions · Answer 4

another alternative is to DTS the tables in foreign key/dependancy order.

put the results of exec sp_MSdependencies in a table, and import the tables/views/procs in that order.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeet SSCommitted Points: 1536 More actions · Answer 5

As stated above you can drop the fk's and then recreate them. Or an alternative is to disable them do the import and then re-enable them.

DISABLE WITH

alter

table department nocheck constraint fk_departmentsection_dsdepartment

ENABLE WITH

alter table department check constraint fk_departmentsection_dsdepartment

Thanks Jeet

steve block SSCertifiable Points: 7698 More actions · Answer 6

I do the following before dts:

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

go

sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

go

After dts:

sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

go

sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"

go

And yes, I did intentionally use CHECK twice when re-enabling the check contraints (which include foreign keys).

Steve

Greg Charles SSC-Forever Points: 45403 More actions · Answer 7

The main reason I use the DROP and CREATE method is so the destination tables can be truncated, which is faster than deleting. Because my DTS package frequently import large tables, using TRUNCATE save a significant amount of time.

Greg