August 19, 2007 at 10:45 pm
I need to drop all foreign key references prior to truncating all the tables and transfering in the data via SSIS from Sybase.
Does anyone have any thoughts on how best to drop the fkeys and dynamically recreating them without hardcoding the create and drop statements?
August 19, 2007 at 11:33 pm
As you're on sql2k5, you can disable and then enable the constraints:
-- Disable all table constraints
ALTER
TABLE yourTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER
TABLE yourTable CHECK CONSTRAINT ALL
You would need to create a cursor on select [name] from sys.tables in your db.
However, it is not recommended to get rid of the constraints if you do not have a staging area where to check your data prior loading it into your db, otherwise you can end up with a lot of orphan records.
August 20, 2007 at 1:09 am
Michaela's solution is good, but I'd like to add one thing to it. When you disable a constraint with "NOCHECK" it will do two things. It will disable it, and it will make it non-trusted. When you reenable it with "CHECK", it will not restore the trustedness of the constraint. In order to reenable the constraint and make it trusted you will need to use "WITH CHECK CHECK"
For more information see http://www.simple-talk.com/sql/database-administration/foreign-keys-and-their-states/
Regards,
Andras
August 20, 2007 at 3:23 pm
Disabling was my first thought as well... unfortunately truncate will not work even with fkeys disabled.
My next thought was to extract the source text for the foreign key but the OBJECT_DEFINITION() function will not extract the source text for foreign keys.
I am now looking at writing the information held in sys.foreign_keys to a table and using that information to build create statements dynamically within a stored procedure.
Thanks, Andy
August 21, 2007 at 9:15 am
i saved this snippet for SQl 2000, but it works in 2005;
this creates both the DROP and ADD statements for a db's foreign keys
looking at it, i don't know why it's using dynamic sql, since you could do the same with regular TSQL, but this might help:
CREATE PROCEDURE dbo.spGetFKConstraints
AS
DECLARE @QUERY VARCHAR(8000)
SET @QUERY = '
SELECT CAST(F.NAME AS VARCHAR(255)) AS ForeignKeyName,
CAST(c.name as varchar(255)) AS ForeignTable,
CAST(fc.name as varchar(255)) AS ForeignColumn,
cast(fc2.name as varchar(255)) as ForeignColumn2,
CAST(p.name as varchar(255)) AS PrimaryTable,
CAST(rc.name as varchar(255)) AS PrimaryColumn,
cast(rc2.name as varchar(255)) as PrimaryColumn2
INTO #GetFKConstraints
FROM SYSOBJECTS F
INNER JOIN SYSOBJECTS C ON F.PARENT_OBJ = C.ID
INNER JOIN SYSREFERENCES R on F.ID = R.CONSTID
INNER JOIN SYSOBJECTS P ON R.RKEYID = P.ID
INNER JOIN SYSCOLUMNS RC ON R.RKEYID = RC.ID AND R.RKEY1 = RC.COLID
INNER JOIN SYSCOLUMNS FC ON R.FKEYID = FC.ID AND R.FKEY1 = FC.COLID
left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
WHERE F.TYPE = '+'''F'''+'
--SELECT * from #GetFKConstraints'+'
SELECT ' + '''ALTER TABLE ''' + '+ ForeignTable + ' + '''
DROP CONSTRAINT ''' + '+ ForeignKeyName
FROM #GetFKConstraints
ORDER BY ForeignTable'
+'
SELECT ' + '''ALTER TABLE ''' + '+ ForeignTable +' + '''
ADD CONSTRAINT ''' + '+ ForeignKeyName +'
+ ''' FOREIGN KEY ('''+ ' + ForeignColumn + '+ ''')
REFERENCES ''' + '+ PrimaryTable + ' + '''(''' + '+ PrimaryColumn + '+ ''')''' +
'FROM #GetFKConstraints
ORDER BY ForeignTable
'
EXEC (@QUERY)
GO
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply