Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Mapping Error Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 5:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 8:47 AM
Points: 1, Visits: 1
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...
Post #1449938
Posted Monday, May 6, 2013 9:23 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
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.
Post #1449972
Posted Tuesday, May 7, 2013 8:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 1,061, Visits: 2,572
Sergiy (5/6/2013)
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.


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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1450180
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse