Rebuild fk constraints?
-
Is there a way to DROP all my foreign key constraints and then to RE-CREATE them (as I cannot use the truncate method on tables that have fk constraints).
Note: I tried:
sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
....but this doesn't work as it only disable the constraints temporarily.
-
The easiest way I've found is to use SQL-DMO, but it's not trivial.
--Jonathan
--Jonathan -
How about this?
select 'ALTER TABLE ' + cast(table_name as char(30)) + 'DROP CONSTRAINT '
+ cast(constraint_name as char(50)) + char(13) + 'go' + char(13)
from information_schema.constraint_table_usage
where constraint_name like 'FK%'
order by table_name, constraint_name
Greg
Greg
-
Greg, this may DROP the constraints but I cant see how I can re-create them afterwards.
-
Run this script and save the results before you run the 'drop' script.
select 'ALTER TABLE ' + cast(t.name as char(30)) + 'ADD CONSTRAINT '
+ cast(c.name as char(50)) + 'FOREIGN KEY ' + '(' + fc.name + ') ' + 'REFERENCES '
+ cast(rt.name as char(30)) + '(' + rc.name + ') ' + 'go'
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
-
Hey Eamon this sript was on this site a few weeks ago. I've used it a number of times and it worked quite well.
CREATE view sysFKeys as
select cast(f.name as varchar(255)) as foreign_key_name
, r.keycnt
, cast(c.name as varchar(255)) as foreign_table
, cast(fc.name as varchar(255)) as foreign_column_1
, cast(fc2.name as varchar(255)) as foreign_column_2
, cast(p.name as varchar(255)) as primary_table
, cast(rc.name as varchar(255)) as primary_column_1
, cast(rc2.name as varchar(255)) as primary_column_2
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'
go
/* Drop foreignkey constraint from table */
select 'ALTER TABLE ' + foreign_table + '
DROP CONSTRAINT ' + foreign_key_name
from sysfkeys order by foreign_table
/* ADD foreignKey constraints to a table */
select 'ALTER TABLE ' + foreign_table + ' ADD CONSTRAINT ' + foreign_key_name + '
FOREIGN KEY (' + foreign_column_1 + ')
REFERENCES ' + primary_table + ' (' + primary_column_1 + ')'
from sysfkeys order by foreign_table
-
Eoin and Greg
Thanks for your replies. I actually tried out Gregs script first before I saw Eoin's one. It worked, very well.
Eoins script looks very similar. Thank you both.
Eamon
Edited by - eamonroche on 12/19/2003 03:51:11 AM
-
Many problems with these scripts:
- Keys can have up to 16 columns. The first script allows only one, the second allows only two.
- Object names need not be regular. All the above scripts do not allow for this possibility.
- Foreign keys can include DRI options (e.g. ON UPDATE CASCADE), which will not be included in the above scripts.
- There are other possible options (e.g. NO CHECK, NOT FOR REPLICATION) that are also not included in the above.
Not being one to be totally negative, below is a UDF and script that fixes these issues. Much as I dislike directly accessing the system tables (and all bets are off in any version other than SQL Server 2000, which I used to test), the ANSI schema views do not contain enough information to do this. For that reason, I also used the undocumented bitmap values of the Status column rather than available metadata functions like OBJECTPROPERTY: we've already got the row from the system table, so we might as well use it directly rather than indirectly.
CREATE FUNCTION dbo.f_fkCols(@keyid int)
RETURNS varchar(8000) BEGIN
DECLARE @fk varchar(8000), @rk varchar(8000)
SELECT @fk = ISNULL(@fk + ',','') + QUOTENAME(cf.name), @rk = ISNULL(@rk + ',','') + QUOTENAME(cr.name)
FROM sysforeignkeys f JOIN syscolumns cf ON f.fkeyid = cf.id and f.fkey = cf.colid
JOIN syscolumns cr ON f.rkeyid = cr.id and f.rkey = cr.colid
WHERE f.constid = @keyid
ORDER BY keyno
RETURN @fk + '.' + @rk ENDI used a period as delimiter because that would make parsing the returned string easy with PARSENAME(), but I later realized that the 128 character limit on each part was too small. You could instead write two UDFs, one for the foreign key columns and one for the referenced key columns, or perhaps return a two row table by using UNION.
SELECT DISTINCT 'ALTER TABLE ' + QUOTENAME(f.name) + ' ADD ' +
case when c.status & 256 = 256 then 'NOCHECK ' else '' end +
'CONSTRAINT ' + QUOTENAME(c.name) + ' FOREIGN KEY(' + LEFT(dbo.f_fkcols(k.constid),CHARINDEX('.',dbo.f_fkcols(k.constid))-1)
+ ') REFERENCES ' + QUOTENAME(r.name) + '(' + RIGHT(dbo.f_fkcols(k.constid),CHARINDEX('.',REVERSE(dbo.f_fkcols(k.constid)))-1) + ')'
+ CASE WHEN c.status & 4096 = 4096 THEN ' ON DELETE CASCADE' ELSE '' END
+ CASE WHEN c.status & 8192 = 8192 THEN ' ON UPDATE CASCADE' ELSE '' END
+ CASE WHEN c.status & 1024 = 1024 THEN ' NOT FOR REPLICATION' ELSE '' END
from sysforeignkeys k JOIN sysobjects c ON k.constid = c.id
join sysobjects f on k.fkeyid = f.id
join sysobjects r on k.rkeyid = r.idOne could write the scripts directly to a file (which is natural with SQL-DMO) by turning the script into a nonparamterized stored procedure and using bcp (if you allow use of xp_cmdshell), e.g.:
CREATE PROC dbo.p_ScriptFKs AS
SET NOCOUNT ON
SELECT 'ALTER TABLE ' + QUOTENAME(f.Name) + ' ADD' +
CASE WHEN c.Status & 256 = 256 THEN ' NOCHECK' ELSE '' END +
' CONSTRAINT ' + QUOTENAME(c.Name) + ' FOREIGN KEY(' + LEFT(dbo.f_fkcols(k.ConstId),CHARINDEX('.',dbo.f_fkCols(k.ConstId))-1)
+ ') REFERENCES ' + QUOTENAME(r.Name) + '(' + RIGHT(dbo.f_fkcols(k.ConstId),CHARINDEX('.',REVERSE(dbo.f_fkCols(k.ConstId)))-1) + ')'
+ CASE WHEN c.Status & 4096 = 4096 THEN ' ON DELETE CASCADE' ELSE '' END
+ CASE WHEN c.Status & 8192 = 8192 THEN ' ON UPDATE CASCADE' ELSE '' END
+ CASE WHEN c.Status & 1024 = 1024 THEN ' NOT FOR REPLICATION' ELSE '' END
FROM
(SELECT DISTINCT ConstId, fKeyId, rKeyId
FROM SysForeignKeys) k JOIN SysObjects c ON k.ConstId = c.Id
JOIN SysObjects f ON k.fKeyId = f.Id
JOIN SysObjects r ON k.rKeyId = r.Id
DECLARE @qry varchar(400), @db sysname, @path varchar(128)
SET @db = db_name()
SET @path = 'C:\'
SET @qry = 'bcp "exec ' + @db + '.dbo.p_ScriptFKs" queryout "' + @path + 'makefks.sql" -c -T'
EXEC master.dbo.xp_cmdshell @qryIf you do this, it would also be a simple matter to write another proc for your drops and truncates:
CREATE PROC dbo.p_DropTruncFKs AS
SET NOCOUNT ON
SELECT DISTINCT 'ALTER TABLE ' + QUOTENAME(f.Name) + ' DROP CONSTRAINT ' + QUOTENAME(c.Name) +
' TRUNCATE TABLE ' + QUOTENAME(f.Name)
FROM SysForeignKeys k JOIN SysObjects c ON k.ConstId = c.Id
JOIN SysObjects f ON k.fKeyId = f.IdThen you could use a script or stored procedure to call these stored procedures and even exceute the saved scripts with osql.
I still recommend using SQL-DMO for this; perhaps now you can undestand why.
--Jonathan
--Jonathan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply