The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 1

[icemel]

SQL Server 2005

Having problems dropping/recreating a Foreign Key:

When I do this:

ALTER TABLE customerNPaccount
DROP CONSTRAINT FK_CustomerNPAccount_CUSTOMER

It results in:

Msg 3728, Level 16, State 1, Line 1
'FK_CustomerNPAccount_CUSTOMER' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

OK - but when I try to add it:

ALTER TABLE customerNPaccount
ADD CONSTRAINT FK_CustomerNPAccount_CUSTOMER FOREIGN KEY (customerNo) REFERENCES customer(customerNo)

Results in:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_CustomerNPAccount_CUSTOMER". The conflict occurred in database "myDatabase", table "dbo.CUSTOMER", column 'CUSTOMERNO'.

So, does or does not this Foreign Key exist???

Am trying to find a system table or sp that will will ALL FOREIGN KEYS for a given database... is this possible?

sp_help does not yield any clues.

THANKS

 

[vongrunt]

> So, does or does not this Foreign Key exist???

No. Creation fails because some foreign values do not exist in primary table. First error message is kind of misleading - it is same even if constraint does not exist.

> Am trying to find a system table or sp that will will ALL FOREIGN KEYS for a given database... is this possible?

Table sysforeignkeys, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view, sp_fkeys sproc... there many ways.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Oops... sysforeignkeys is SQL2000 only ... check sys.* stuff instead.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[icemel]

Hi,

so you're saying:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_CustomerNPAccount_CUSTOMER". The conflict occurred in database "myDatabase", table "dbo.CUSTOMER", column 'CUSTOMERNO'.

happens because the there are missing values in the primary key table?

yes, the error msg doesn't really make sense

i'll take a look

thanx

 

[vongrunt]

Yup. Use query with left outer join to find missing values:

select A.*
from foreigntable A
left outer join primarytable B on A.foreignkey = B.primarykey
where B.primarykey is null

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[icemel]

yes - thanks