Hello,
I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.
Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).
Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).
After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:
Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.
'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.
When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:
ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)
Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.
(Note: Each time I do this, the 8 hexadecimal character suffix changes.)
When I try to drop the foreign key:
alter table Person
drop constraint FK__Person__LocationId
it comes back with the error:
Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.
So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?
Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )
create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)
create table location
(
locationId int constraint PKlocation primary key
)
ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.
Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.
insert into location
values (1)
insert into location
values (2)
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)
to see the constraints and the columns you can use this query:
select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'
Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.
|||Thanks Louis for the useful info. It helped in examining the problem more closely.
In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.
|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||Hi all,
I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.
And I can't drop and recreate table.
How can I do ?
|||The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:
select *
from Person as p
where not exists(
select * from Location as l
where l.LocationId = p.LocationId
)
But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.
