Adding Foreign Keys

Question

Adding Foreign Keys

john-902052

SSCommitted

Points: 1578
More actions
August 20, 2011 at 10:44 pm

#243544

Hey all,
I have a question if you wouldn't mind.
I have a db Cases with an Identity column CaseID and I have a few other dbs that store the CaseID. Up to this point I have been taking care of the referential integrity in my app but am moving to Entity Framework so I need the foreign keys in the DB. I am stumped.
I go into the Cases DB and try to create the FK but it keeps complaining about a unique ID issue.
Primary KeyTable CaseTypes field is CaseTypeID FK Table is Cases field is CaseTypeID.
'CaseTypes' table saved successfully
'Cases' table
- Unable to create relationship 'FK_Cases_CaseTypes'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Cases_CaseTypes". The conflict occurred in database "-------", table "dbo.CaseTypes", column 'CaseTypeID'.
They are both identity columns?????????????
:crazy:

Viewing 5 posts - 1 through 4 (of 4 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

john-902052 (8/20/2011)
Hey all,
I have a question if you wouldn't mind.
I have a db Cases with an Identity column CaseID and I have a few other dbs that store the CaseID. Up to this point I have been taking care of the referential integrity in my app but am moving to Entity Framework so I need the foreign keys in the DB. I am stumped.
I go into the Cases DB and try to create the FK but it keeps complaining about a unique ID issue.
Primary KeyTable CaseTypes field is CaseTypeID FK Table is Cases field is CaseTypeID.
'CaseTypes' table saved successfully
'Cases' table
- Unable to create relationship 'FK_Cases_CaseTypes'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Cases_CaseTypes". The conflict occurred in database "-------", table "dbo.CaseTypes", column 'CaseTypeID'.
They are both identity columns?????????????
:crazy:

Please provide your DDL to include Primary Keys, Unique and Foreign Key Constraints, etc.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 2

john-902052 (8/20/2011)
Hey all,
I have a question if you wouldn't mind.
I have a db Cases with an Identity column CaseID and I have a few other dbs that store the CaseID. Up to this point I have been taking care of the referential integrity in my app but am moving to Entity Framework so I need the foreign keys in the DB. I am stumped.
I go into the Cases DB and try to create the FK but it keeps complaining about a unique ID issue.
Primary KeyTable CaseTypes field is CaseTypeID FK Table is Cases field is CaseTypeID.
'CaseTypes' table saved successfully
'Cases' table
- Unable to create relationship 'FK_Cases_CaseTypes'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Cases_CaseTypes". The conflict occurred in database "-------", table "dbo.CaseTypes", column 'CaseTypeID'.
They are both identity columns?????????????
:crazy:

So your application managed DRI didn't do a good job :sick:

Your cases table is using a case type value that doesn't exist in the casetypes table.

To detect the issues at data level, just write a query like:

select * from cases C where not exists ( select * from casetypes CT where CT.casetypeid = C.casetypeid)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I should have caught that, it is getting late.

You are trying to create a FK Constraint on a child tabe record(s) where there is not a correspnding record in the parent table.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

john-902052 SSCommitted Points: 1578 More actions · Answer 4

Your correct it was a lookup table that I added later on in the project and didn't go back and fix the old records.

Thanks so much.