May 6, 2013 at 5:44 pm
Hi
I am actually trying to make relationships in an already designed database..
Database is historic one and no proper relationships, I could find 3 PK's and 0 FK's. I am doing it in test database which lives in Prod and which the data is already there.
My requirement is I have to make a Database Diagram by assigning proper constraints. I had to find out 1100 common columns in 158 tables and I was able to make relationships for very few tables but not all of them.
Following are the errors while saving
..............
- Unable to create relationship 'FK_tblStatTenancyInfo_tblStatInfo'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblStatTenancyInfo_tblStatInfo". The conflict occurred in database "HFSMart", table "dbo.tblStatInfo", column 'StatTenancyAddress'.
This is an example error and is occurring for all other tables whenever I make relationships. I'm sure There are no keys before making relationships but still it's getting conflicted.
Please note I am making relationships in already existence database which is a copy of production database and data is already there.
Do you think this error causes because of the data already exists or any idea for the better solution.
Could any one respond to my query ASAP as I am facing similar errors for all the tables..
Many Thanks...
May 6, 2013 at 9:23 pm
Try to run queries like this:
select * from FK_Table F
WHERE NOT EXISTS (
select * from PK_Table P
where P.PK_Column1 = F.FK_Column1 and P.PK_Column2 = F.FK_Column2
It should give you the entries (so called "orhaned records") in "FK_Table" which prevent you from creating the FK.
Inspect them and decide what you can do about it:
- delete orhaned records from FK_Table;
- add missing kes to PK_Table;
- reconsider the FK.
_____________
Code for TallyGenerator
May 7, 2013 at 8:29 am
Sergiy (5/6/2013)
Try to run queries like this:
select * from FK_Table FWHERE NOT EXISTS (
select * from PK_Table P
where P.PK_Column1 = F.FK_Column1 and P.PK_Column2 = F.FK_Column2
It should give you the entries (so called "orhaned records") in "FK_Table" which prevent you from creating the FK.
Inspect them and decide what you can do about it:
- delete orhaned records from FK_Table;
- add missing kes to PK_Table;
- reconsider the FK.
modrhafi.sk, Sergiy's code will help you find and solve problems in the data, but I think you might need a clear explanation of the problem first! You have rows in your tables with FK constraints where the key values do not exist in the referenced tables. When you attempt to create the FK constraint, the effort fails because SQL Server can't create a FK constraint that will be violated by data already in the tables.
An example of your situation:
TableB:
BID someColumn
1 xyz
2 abc
3 mno
TableA:
AID someOtherColumn
1 987654
2 654321
In this situation, you cannot add a FK constraint on TableB.BID referencing TableA.AID because there is a value, 3, in TableB.BID that does not exist in TableA.AID.
Sergiy's code will help you find the rows like these that are hindering your efforts to add FK constraints. Since those rows exist, though, you may need to review whether you have correctly derived the relations - do the FK constraints that you are trying to create really reflect business requirements?
Jason Wolfkill
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply