SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Mapping Error


Database Mapping Error

Author
Message
mohdrafi.sk
mohdrafi.sk
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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...
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25401 Visits: 12464
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.
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2672 Visits: 2582
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search