Remove duplicate Foreign Keys

Question

Remove duplicate Foreign Keys

Lowell

SSC Guru

Points: 323518
More actions
March 4, 2004 at 2:24 pm

#107225

I'm trying to get my arms around how to identify and remove all duplicated foreign keys in a database.
In one of the developers databases, someone clearly ran a script to insert all the foreign keys in the database more than once, and I'd like to make a catch all script to remove them;
here's an example:

CREATE TABLE [CMCONT] (
[CONTACTTBLKEY] int NOT NULL PRIMARY KEY,
[LNAME] varchar (50) NULL,
[FNAME] varchar (20) NULL,
[ADDRTBLKEY] int NOT NULL )
CREATE TABLE [CMADDR] (
[ADDRTBLKEY] int NOT NULL PRIMARY KEY,
[ADDR1] varchar (50) NULL,
[ADDR2] varchar (50) NULL,
[CITY] varchar (20) NULL,
[STATE] varchar (2) NULL,
[ZIPCODE] varchar (9) NULL)
--fk
ALTER TABLE CMCONT ADD FOREIGN KEY(ADDRTBLKEY) REFERENCES CMADDR(ADDRTBLKEY)
--duplicate fk
ALTER TABLE CMCONT ADD FOREIGN KEY(ADDRTBLKEY) REFERENCES CMADDR(ADDRTBLKEY)
--duplicate fk 2
ALTER TABLE CMCONT ADD FOREIGN KEY(ADDRTBLKEY) REFERENCES CMADDR(ADDRTBLKEY)

My first attempt was to look at sysforeignkeys with this script, and it seems to identify the items to remove....
select fkeyid,rkeyid,fkey,rkey,count(fkeyid) as NumDuplicates from sysforeignkeys group by fkeyid,rkeyid,fkey,rkey,keyno
having count(fkeyid) > 1
How do I remove all but one FK from the result set? I could use a cursor to loop thru I know, but I thought it might be easier than that, but I can't seem to visualize it.

Thanks!
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!

Viewing 3 posts - 1 through 2 (of 2 total)

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

chrisleonard SSCertifiable Points: 5030 More actions · Answer 1

OK, it's late here, so somebody may well jump in with a much better idea, but if you don't mind a little cut-and-paste, run the following query and then execute its output:

select 'alter table ' + object_name(fkeyid)
     + ' drop constraint ' + object_name(constid)
from   sysforeignkeys fk1
where exists
(
select fkeyid,rkeyid,fkey,rkey,count(fkeyid) as NumDuplicates
from   sysforeignkeys fk2
where fk1.fkeyid = fk2.fkeyid
and    fk1.rkeyid = fk2.rkeyid
and    fk1.fkey   = fk2.fkey
and    fk1.rkey   = fk2.rkey
and    fk1.keyno = fk2.keyno
group by fkeyid,rkeyid,fkey,rkey,keyno
having count(fkeyid) > 1
and    fk1.constid != min(fk2.constid)
)

I think that will get the commands you want to run. Will that work for you?

By the way, for the technically curious and the theoretically inclined, the "fk1.constid != min(fk2.constid)" bit is a slightly unusual join type, isn't it?

Cheers,

Chris

Darek Tomyn SSCrazy Points: 2551 More actions · Answer 2

I personally run the below script that I created... who knows, I may be way off on this, but I found that other queries I found on the internet would not get all of the constraints if they were complex...

--The below SQL will output a large alter table script to remove duplicate

--and triplicate constraints in a database...

--basis of this query comes from Microsoft's undocumented stored procedure "sp_MStablerefs"

--had considered just putting those results into a temp table, but this way can see exactly

--what is going on (or at least can see what is going on a bit better and can customize it if needed)

DECLARE @tablename AS SYSNAME

--can either provide a name or leave it as null

--SET @tablename = 't_upc'

SET @tablename = null

SELECT

'ALTER TABLE ' + user_name(FKT.uid) + '.' + FKT.name + ' DROP CONSTRAINT ' + object_name(r.constid)--,

-- N'PK_Table' = PKT.name,

-- N'Constraint' = object_name(r.constid),

-- cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1))

from dbo.sysreferences r

INNER JOIN dbo.sysconstraints c ON

r.constid = c.constid

INNER JOIN dbo.sysobjects PKT ON

PKT.id = r.rkeyid

INNER JOIN dbo.sysobjects FKT ON

FKT.id = r.fkeyid

INNER JOIN

(

SELECT

N'PK_Table' = PKT.name,

N'FK_Table' = FKT.name,

cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),

cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),

cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),

cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),

cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),

cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),

cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),

cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),

cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),

cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),

cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),

cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),

cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),

cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),

cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),

cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16)),

cRefCol1 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey1)),

cRefCol2 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey2)),

cRefCol3 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey3)),

cRefCol4 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey4)),

cRefCol5 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey5)),

cRefCol6 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey6)),

cRefCol7 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey7)),

cRefCol8 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey8)),

cRefCol9 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey9)),

cRefCol10 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey10)),

cRefCol11 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey11)),

cRefCol12 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey12)),

cRefCol13 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey13)),

cRefCol14 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey14)),

cRefCol15 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey15)),

cRefCol16 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey16)),

N'PK_Table_Owner' = user_name(PKT.uid),

N'FK_Table_Owner' = user_name(FKT.uid)

from dbo.sysreferences r

INNER JOIN dbo.sysconstraints c ON

r.constid = c.constid

INNER JOIN dbo.sysobjects PKT ON

PKT.id = r.rkeyid

INNER JOIN dbo.sysobjects FKT ON

FKT.id = r.fkeyid

where

(@tablename is null or

(r.rkeyid = object_id(@tablename) or r.fkeyid = object_id(@tablename)))

GROUP BY

PKT.name,

FKT.name,

convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),

convert(nvarchar(132), col_name(r.fkeyid, r.fkey16)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey1)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey2)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey3)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey4)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey5)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey6)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey7)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey8)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey9)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey10)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey11)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey12)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey13)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey14)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey15)),

convert(nvarchar(132), col_name(r.rkeyid, r.rkey16)),

PKT.uid,

FKT.uid

HAVING COUNT(*) > 1

) x ON

x.PK_Table = PKT.name AND

x.FK_Table = FKT.name AND

x.cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)) AND

ISNULL(x.cKeyCol2, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)), '') AND

ISNULL(x.cKeyCol3, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)), '') AND

ISNULL(x.cKeyCol4, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)), '') AND

ISNULL(x.cKeyCol5, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)), '') AND

ISNULL(x.cKeyCol6, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)), '') AND

ISNULL(x.cKeyCol7, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)), '') AND

ISNULL(x.cKeyCol8, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)), '') AND

ISNULL(x.cKeyCol9, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)), '') AND

ISNULL(x.cKeyCol10, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)), '') AND

ISNULL(x.cKeyCol11, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)), '') AND

ISNULL(x.cKeyCol12, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)), '') AND

ISNULL(x.cKeyCol13, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)), '') AND

ISNULL(x.cKeyCol14, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)), '') AND

ISNULL(x.cKeyCol15, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)), '') AND

ISNULL(x.cKeyCol16, '') = ISNULL(convert(nvarchar(132), col_name(r.fkeyid, r.fkey16)), '') AND

ISNULL(x.cRefCol1, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey1)), '') AND

ISNULL(x.cRefCol2, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey2)), '') AND

ISNULL(x.cRefCol3, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey3)), '') AND

ISNULL(x.cRefCol4, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey4)), '') AND

ISNULL(x.cRefCol5, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey5)), '') AND

ISNULL(x.cRefCol6, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey6)), '') AND

ISNULL(x.cRefCol7, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey7)), '') AND

ISNULL(x.cRefCol8, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey8)), '') AND

ISNULL(x.cRefCol9, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey9)), '') AND

ISNULL(x.cRefCol10, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey10)), '') AND

ISNULL(x.cRefCol11, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey11)), '') AND

ISNULL(x.cRefCol12, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey12)), '') AND

ISNULL(x.cRefCol13, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey13)), '') AND

ISNULL(x.cRefCol14, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey14)), '') AND

ISNULL(x.cRefCol15, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey15)), '') AND

ISNULL(x.cRefCol16, '') = ISNULL(convert(nvarchar(132), col_name(r.rkeyid, r.rkey16)), '') AND

x.PK_Table_Owner = user_name(PKT.uid) AND

x.FK_Table_Owner = user_name(FKT.uid)

where

(@tablename is null or

(r.rkeyid = object_id(@tablename) or r.fkeyid = object_id(@tablename)))

AND r.constid NOT IN

(

SELECT TOP 1 r2.constid

from dbo.sysreferences r2

INNER JOIN dbo.sysconstraints c2 ON

r2.constid = c2.constid

INNER JOIN dbo.sysobjects PKT2 ON

PKT2.id = r2.rkeyid

INNER JOIN dbo.sysobjects FKT2 ON

FKT2.id = r2.fkeyid