Creating FK's and got this error on one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating FK's and got this error on one

Author

Topic

tmcrouse
Starting Member

12 Posts

Posted - 2015-02-03 : 15:02:00

ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Performan__Uniqu__43D61337". The conflict occurred in database "ANTHEMQ", table "dbo.Program", column 'Unique_ID'.

I ran the same addition of an FK on another table that uses the same process and happens to have this unique_id field in it. Here is the query I ran on that table and all went fine.

ALTER TABLE anthemq.dbo.qualmain
ADD FOREIGN KEY (unique_ID)
REFERENCES program (unique_ID);

but now doing the same on performance and I get that error. The datatype for the program, qualmain and performance is all
the same all are not null

ALTER TABLE anthemq.dbo.performanceguarantee
ADD FOREIGN KEY (unique_ID)
REFERENCES program (unique_ID);

tmc

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 15:40:24

When you add the FK, Sql is checking the current values in your table to ensure referential integrity. So, there is some unique_ID in table qualmain for which ther eis no uniq_id in table performanceguarantee

Check it with this:


select * from anthemq.dbo.performanceguarantee q
where not exists (select 1 from program p where p.unique_id = q.uniqueId)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources