Foreign Key/Primary Key GRRR!

Question

Foreign Key/Primary Key GRRR!

Shark Energy

SSCrazy Eights

Points: 8389
More actions
May 23, 2007 at 10:20 am

#62055

I want to truncate a bunch of tables, and bcp in replacement data from another server. The primary keys and foreign keys are causing me no end of grief and whichever order I seem to drop/add them in. Any tips?

Viewing 15 posts - 1 through 15 (of 19 total)

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

I'd be a little concerned if I were you. The key constraints are there to preserve data integrity. I'd recommend you fix your data first so that it doesn't violate the constraints. If that can't be done, you can drop them and re-add the constraints using the WITH NOCHECK clause, but that can lead to problems later when you expect strong data integrity.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

If you are doing a complete refresh of the data you need to complete the drop and add in the following order:

Starting with the lowest child table, start deleting records moving up the chain (you may need to go across children before going up to each of the parent tables)

Once all data is deleted, you start adding data in the reverse order starting with the top parent table and working your way down and across.

Hope this helps!

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 3

Its going to take forever! There are like 25 tables in place just for me to update this 1 tables data!

Oh well.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

The quick way: script out your foreign keys, drop them, truncate the tables, reload the data, replace the foreign keys.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

It is better to bcp the data into staging tables, and then update rows that already exist that have changed, and insert the new rows.

Paul Rubel Ten Centuries Points: 1047 More actions · Answer 6

The Embaradarro tools allow you to disable the foreign key. It'll take 5 sec's with the tool.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 7

It may be frustrating and taking seemingly forever, but usually when the system balks at what you are trying to do, it is trying to tell you something important.

The first thing I would do is generate a diagram with those 25 tables and take the time to trace out the referential flow (a picture being worth... a lot). I can almost guarantee you will learn something you didn't know and should know.

I'm guessing, of course, as you have not provided any information as to the messages you've been getting. 😉

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

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

I do this all the time to get production data into dev tables. Follow Lynn's advice and you won't be sorry.

Greg

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 9

Hi,

You can disable first all FK (constraints) and after you populate with respect to constraints, your tables, you should enable them. Here is the code:

SET QUOTED_IDENTIFIER OFF

GO

-- Disable

EXECUTE sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

GO

-- Here you manipulate your data

-- Enable

EXECUTE sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

GO

In Theory, theory and practice are the same...In practice, they are not.

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 10

I think it's pretty easy!

In Theory, theory and practice are the same...In practice, they are not.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

If you are planning on doing this in a production database where there are live transactions happening, dropping or disabling the foreign key constraints is a very bad idea. You can end up causing massive damage to your data, and may not know it till much later with a huge effort to try to fix your data.

The safe way to do it is what I suggested earlier. BCP the data into staging tables, and then insert the new rows, and update the rows that have changed. You will have to apply the inserts and updates in the correct order. You can use the script on the link below to determine the correct order. Start with tables at reference level 0, and then do higher levels in order from lowest to highest.

Find Table Reference Levels:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 12

maybe this script can help you a bit :

I used it to convert primary keys to be clustered.

So just exclude the drop/create primary key part and off you go ..

- TEST IT - TEST IT - TEST IT -

create procedure Spc_ALZDBA_PK2Clustered

@ParentTbName varchar(128)

, @ParentTbOwner varchar(128) = NULL

as

begin

SET nocount on

create table #tmpPKeys(

TABLE_QUALIFIER sysname,

TABLE_OWNER sysname not null,

TABLE_NAME sysname not null,

COLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

PK_NAME sysname null )

Create index ix#tmpPKeys on #tmpPKeys (TABLE_QUALIFIER, TABLE_OWNER,TABLE_NAME, KEY_SEQ)

-- Get PK-info

insert into #tmpPKeys

exec sp_pkeys @table_name = @ParentTbName

, @table_owner = @ParentTbOwner

-- [ , [ @table_qualifier = ] 'qualifier' ] -- DBName

create table #tmpFKeys

(PKTABLE_QUALIFIER sysname not null,

PKTABLE_OWNER sysname not null,

PKTABLE_NAME sysname not null,

PKCOLUMN_NAME sysname not null,

FKTABLE_QUALIFIER sysname not null,

FKTABLE_OWNER sysname not null,

FKTABLE_NAME sysname not null,

FKCOLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

UPDATE_RULE smallint not null,

DELETE_RULE smallint not null,

FK_NAME sysname not null,

PK_NAME sysname not null,

DEFERRABILITY int not null)

Create index #tmpFKeys on #tmpFKeys (FK_NAME, KEY_SEQ)

-- Get FK-info (all dependant objects)

insert into #tmpFKeys

exec sp_fkeys @pktable_name = @ParentTbName

, @pktable_owner = @ParentTbOwner

-- [ , [ @pktable_qualifier = ] 'pktable_qualifier' ]

-- { , [ @fktable_name = ] 'fktable_name' }

-- [ , [ @fktable_owner = ] 'fktable_owner' ]

-- [ , [ @fktable_qualifier = ] 'fktable_qualifier' ]

print 'Begin transaction trxAlter_' + @ParentTbName

print ' '

print '-- Drop Referencing constraints'

select 'Alter Table [' + FKTABLE_OWNER + '].[' + FKTABLE_NAME + '] drop constraint [' + FK_NAME + ']' + char(13) + 'GO '

from #tmpFKeys

where Key_SEQ = 1

order by FKTABLE_OWNER, FKTABLE_NAME, FK_NAME

print '-- drop PK-constraint'

select 'Alter Table [' + TABLE_OWNER + '].[' + TABLE_NAME + '] drop constraint [' + PK_NAME + ']' + char(13) + 'GO '

from #tmpPKeys

where Key_SEQ = 1

order by TABLE_OWNER, TABLE_NAME

print '-- Create PK_Constraint'

select 'Alter Table [' + P1.TABLE_OWNER + '].[' + P1.TABLE_NAME + '] WITH NOCHECK add constraint ' + P1.PK_NAME + ' PRIMARY KEY CLUSTERED ( '+

P1.COLUMN_NAME

+ case when P2.COLUMN_NAME is null then '' else ', ' + P2.COLUMN_NAME end

+ case when P3.COLUMN_NAME is null then '' else ', ' + P3.COLUMN_NAME end

+ case when P4.COLUMN_NAME is null then '' else ', ' + P4.COLUMN_NAME end

+ case when P5.COLUMN_NAME is null then '' else ', ' + P5.COLUMN_NAME end

+ case when P6.COLUMN_NAME is null then '' else ', ' + P6.COLUMN_NAME end

+ case when P7.COLUMN_NAME is null then '' else ', ' + P7.COLUMN_NAME end

+ case when P8.COLUMN_NAME is null then '' else ', ' + P8.COLUMN_NAME end

+ case when P9.COLUMN_NAME is null then '' else ', ' + P9.COLUMN_NAME end

+ case when P10.COLUMN_NAME is null then '' else ', ' + P10.COLUMN_NAME end

+ case when P11.COLUMN_NAME is null then '' else ', ' + P11.COLUMN_NAME end

+ case when P12.COLUMN_NAME is null then '' else ', ' + P12.COLUMN_NAME end

+ case when P13.COLUMN_NAME is null then '' else ', ' + P13.COLUMN_NAME end

+ case when P14.COLUMN_NAME is null then '' else ', ' + P14.COLUMN_NAME end

+ case when P15.COLUMN_NAME is null then '' else ', ' + P15.COLUMN_NAME end

+ case when P16.COLUMN_NAME is null then '' else ', ' + P16.COLUMN_NAME end

+ case when P17.COLUMN_NAME is null then '' else ', ' + P17.COLUMN_NAME end

+ case when P18.COLUMN_NAME is null then '' else ', ' + P18.COLUMN_NAME end

+ ' ) ' + char(13) + 'GO '

from #tmpPKeys P1

left join #tmpPKeys P2

on P1.TABLE_OWNER = P2.TABLE_OWNER and P1.TABLE_NAME = P2.TABLE_NAME and P1.KEY_SEQ = 1 and P2.KEY_SEQ = 2

left join #tmpPKeys P3

on P1.TABLE_OWNER = P3.TABLE_OWNER and P1.TABLE_NAME = P3.TABLE_NAME and P1.KEY_SEQ = 1 and P3.KEY_SEQ = 3

left join #tmpPKeys P4

on P1.TABLE_OWNER = P4.TABLE_OWNER and P1.TABLE_NAME = P4.TABLE_NAME and P1.KEY_SEQ = 1 and P4.KEY_SEQ = 4

left join #tmpPKeys P5

on P1.TABLE_OWNER = P5.TABLE_OWNER and P1.TABLE_NAME = P5.TABLE_NAME and P1.KEY_SEQ = 1 and P5.KEY_SEQ = 5

left join #tmpPKeys P6

on P1.TABLE_OWNER = P6.TABLE_OWNER and P1.TABLE_NAME = P6.TABLE_NAME and P1.KEY_SEQ = 1 and P6.KEY_SEQ = 6

left join #tmpPKeys P7

on P1.TABLE_OWNER = P7.TABLE_OWNER and P1.TABLE_NAME = P7.TABLE_NAME and P1.KEY_SEQ = 1 and P7.KEY_SEQ = 7

left join #tmpPKeys P8

on P1.TABLE_OWNER = P8.TABLE_OWNER and P1.TABLE_NAME = P8.TABLE_NAME and P1.KEY_SEQ = 1 and P8.KEY_SEQ = 8

left join #tmpPKeys P9

on P1.TABLE_OWNER = P9.TABLE_OWNER and P1.TABLE_NAME = P9.TABLE_NAME and P1.KEY_SEQ = 1 and P9.KEY_SEQ = 9

left join #tmpPKeys P10

on P1.TABLE_OWNER = P10.TABLE_OWNER and P1.TABLE_NAME = P10.TABLE_NAME and P1.KEY_SEQ = 1 and P10.KEY_SEQ = 10

left join #tmpPKeys P11

on P1.TABLE_OWNER = P11.TABLE_OWNER and P1.TABLE_NAME = P11.TABLE_NAME and P1.KEY_SEQ = 1 and P11.KEY_SEQ = 11

left join #tmpPKeys P12

on P1.TABLE_OWNER = P12.TABLE_OWNER and P1.TABLE_NAME = P12.TABLE_NAME and P1.KEY_SEQ = 1 and P12.KEY_SEQ = 12

left join #tmpPKeys P13

on P1.TABLE_OWNER = P13.TABLE_OWNER and P1.TABLE_NAME = P13.TABLE_NAME and P1.KEY_SEQ = 1 and P13.KEY_SEQ = 13

left join #tmpPKeys P14

on P1.TABLE_OWNER = P14.TABLE_OWNER and P1.TABLE_NAME = P14.TABLE_NAME and P1.KEY_SEQ = 1 and P14.KEY_SEQ = 14

left join #tmpPKeys P15

on P1.TABLE_OWNER = P15.TABLE_OWNER and P1.TABLE_NAME = P15.TABLE_NAME and P1.KEY_SEQ = 1 and P15.KEY_SEQ = 15

left join #tmpPKeys P16

on P1.TABLE_OWNER = P16.TABLE_OWNER and P1.TABLE_NAME = P16.TABLE_NAME and P1.KEY_SEQ = 1 and P16.KEY_SEQ = 16

left join #tmpPKeys P17

on P1.TABLE_OWNER = P17.TABLE_OWNER and P1.TABLE_NAME = P17.TABLE_NAME and P1.KEY_SEQ = 1 and P17.KEY_SEQ = 17

left join #tmpPKeys P18

on P1.TABLE_OWNER = P18.TABLE_OWNER and P1.TABLE_NAME = P18.TABLE_NAME and P1.KEY_SEQ = 1 and P18.KEY_SEQ = 18

where P1.KEY_SEQ = 1

order by P1.TABLE_OWNER, P1.TABLE_NAME

print '-- Create FK-Constraints'

print '-- keep FK column-ordinal equal to PK column-ordinal'

select 'Alter Table [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] WITH NOCHECK add constraint [' + FK1.FK_NAME + '] FOREIGN KEY ( '+

FK1.FKCOLUMN_NAME

+ case when FK2.FKCOLUMN_NAME is null then '' else ', ' + FK2.FKCOLUMN_NAME end

+ case when FK3.FKCOLUMN_NAME is null then '' else ', ' + FK3.FKCOLUMN_NAME end

+ case when FK4.FKCOLUMN_NAME is null then '' else ', ' + FK4.FKCOLUMN_NAME end

+ case when FK5.FKCOLUMN_NAME is null then '' else ', ' + FK5.FKCOLUMN_NAME end

+ case when FK6.FKCOLUMN_NAME is null then '' else ', ' + FK6.FKCOLUMN_NAME end

+ case when FK7.FKCOLUMN_NAME is null then '' else ', ' + FK7.FKCOLUMN_NAME end

+ case when FK8.FKCOLUMN_NAME is null then '' else ', ' + FK8.FKCOLUMN_NAME end

+ case when FK9.FKCOLUMN_NAME is null then '' else ', ' + FK9.FKCOLUMN_NAME end

+ case when FK10.FKCOLUMN_NAME is null then '' else ', ' + FK10.FKCOLUMN_NAME end

+ case when FK11.FKCOLUMN_NAME is null then '' else ', ' + FK11.FKCOLUMN_NAME end

+ case when FK12.FKCOLUMN_NAME is null then '' else ', ' + FK12.FKCOLUMN_NAME end

+ case when FK13.FKCOLUMN_NAME is null then '' else ', ' + FK13.FKCOLUMN_NAME end

+ case when FK14.FKCOLUMN_NAME is null then '' else ', ' + FK14.FKCOLUMN_NAME end

+ case when FK15.FKCOLUMN_NAME is null then '' else ', ' + FK15.FKCOLUMN_NAME end

+ case when FK16.FKCOLUMN_NAME is null then '' else ', ' + FK16.FKCOLUMN_NAME end

+ case when FK17.FKCOLUMN_NAME is null then '' else ', ' + FK17.FKCOLUMN_NAME end

+ case when FK18.FKCOLUMN_NAME is null then '' else ', ' + FK18.FKCOLUMN_NAME end

+ ' ) REFERENCES [' + FK1.PKTABLE_OWNER + '].[' + FK1.PKTABLE_NAME + '] ( ' +

FK1.PKCOLUMN_NAME

+ case when FK2.PKCOLUMN_NAME is null then '' else ', ' + FK2.PKCOLUMN_NAME end

+ case when FK3.PKCOLUMN_NAME is null then '' else ', ' + FK3.PKCOLUMN_NAME end

+ case when FK4.PKCOLUMN_NAME is null then '' else ', ' + FK4.PKCOLUMN_NAME end

+ case when FK5.PKCOLUMN_NAME is null then '' else ', ' + FK5.PKCOLUMN_NAME end

+ case when FK6.PKCOLUMN_NAME is null then '' else ', ' + FK6.PKCOLUMN_NAME end

+ case when FK7.PKCOLUMN_NAME is null then '' else ', ' + FK7.PKCOLUMN_NAME end

+ case when FK8.PKCOLUMN_NAME is null then '' else ', ' + FK8.PKCOLUMN_NAME end

+ case when FK9.PKCOLUMN_NAME is null then '' else ', ' + FK9.PKCOLUMN_NAME end

+ case when FK10.PKCOLUMN_NAME is null then '' else ', ' + FK10.PKCOLUMN_NAME end

+ case when FK11.PKCOLUMN_NAME is null then '' else ', ' + FK11.PKCOLUMN_NAME end

+ case when FK12.PKCOLUMN_NAME is null then '' else ', ' + FK12.PKCOLUMN_NAME end

+ case when FK13.PKCOLUMN_NAME is null then '' else ', ' + FK13.PKCOLUMN_NAME end

+ case when FK14.PKCOLUMN_NAME is null then '' else ', ' + FK14.PKCOLUMN_NAME end

+ case when FK15.PKCOLUMN_NAME is null then '' else ', ' + FK15.PKCOLUMN_NAME end

+ case when FK16.PKCOLUMN_NAME is null then '' else ', ' + FK16.PKCOLUMN_NAME end

+ case when FK17.PKCOLUMN_NAME is null then '' else ', ' + FK17.PKCOLUMN_NAME end

+ case when FK18.PKCOLUMN_NAME is null then '' else ', ' + FK18.PKCOLUMN_NAME end

+ ' ) ' + char(13) + 'GO '

from #tmpFKeys FK1

left join #tmpFKeys FK2

on FK1.FK_NAME = FK2.FK_NAME and FK1.KEY_SEQ = 1 and FK2.KEY_SEQ = 2

left join #tmpFKeys FK3

on FK1.FK_NAME = FK3.FK_NAME and FK1.KEY_SEQ = 1 and FK3.KEY_SEQ = 3

left join #tmpFKeys FK4

on FK1.FK_NAME = FK4.FK_NAME and FK1.KEY_SEQ = 1 and FK4.KEY_SEQ = 4

left join #tmpFKeys FK5

on FK1.FK_NAME = FK5.FK_NAME and FK1.KEY_SEQ = 1 and FK5.KEY_SEQ = 5

left join #tmpFKeys FK6

on FK1.FK_NAME = FK6.FK_NAME and FK1.KEY_SEQ = 1 and FK6.KEY_SEQ = 6

left join #tmpFKeys FK7

on FK1.FK_NAME = FK7.FK_NAME and FK1.KEY_SEQ = 1 and FK7.KEY_SEQ = 7

left join #tmpFKeys FK8

on FK1.FK_NAME = FK8.FK_NAME and FK1.KEY_SEQ = 1 and FK8.KEY_SEQ = 8

left join #tmpFKeys FK9

on FK1.FK_NAME = FK9.FK_NAME and FK1.KEY_SEQ = 1 and FK9.KEY_SEQ = 9

left join #tmpFKeys FK10

on FK1.FK_NAME = FK10.FK_NAME and FK1.KEY_SEQ = 1 and FK10.KEY_SEQ = 10

left join #tmpFKeys FK11

on FK1.FK_NAME = FK11.FK_NAME and FK1.KEY_SEQ = 1 and FK11.KEY_SEQ = 11

left join #tmpFKeys FK12

on FK1.FK_NAME = FK12.FK_NAME and FK1.KEY_SEQ = 1 and FK12.KEY_SEQ = 12

left join #tmpFKeys FK13

on FK1.FK_NAME = FK13.FK_NAME and FK1.KEY_SEQ = 1 and FK13.KEY_SEQ = 13

left join #tmpFKeys FK14

on FK1.FK_NAME = FK14.FK_NAME and FK1.KEY_SEQ = 1 and FK14.KEY_SEQ = 14

left join #tmpFKeys FK15

on FK1.FK_NAME = FK15.FK_NAME and FK1.KEY_SEQ = 1 and FK15.KEY_SEQ = 15

left join #tmpFKeys FK16

on FK1.FK_NAME = FK16.FK_NAME and FK1.KEY_SEQ = 1 and FK16.KEY_SEQ = 16

left join #tmpFKeys FK17

on FK1.FK_NAME = FK17.FK_NAME and FK1.KEY_SEQ = 1 and FK17.KEY_SEQ = 17

left join #tmpFKeys FK18

on FK1.FK_NAME = FK18.FK_NAME and FK1.KEY_SEQ = 1 and FK18.KEY_SEQ = 18

where FK1.KEY_SEQ = 1

order by FK1.FKTABLE_OWNER, FK1.FKTABLE_NAME, FK1.FK_NAME

print '-- Create FK-Indexes'

print '-- keep FK column-ordinal / order equal to PK column-ordinal / order (asc/desc)'

select 'Create index [XFK_' + FK1.FK_NAME + '] on [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] ( '+