Misleading error message when you create foreign key constraint
When you create new foreign key constraint in SQL Server 2005 and this constraint conflict with the data already exists in the tables, you will get an error message that might be misleading. Let's say that you have 2 tables (depts and employee) and you want to create a foreign key constraint in the employee table that references the depts table. If you wrote this script
ALTER TABLE dbo.employees ADD CONSTRAINT
FK_employees_depts FOREIGN KEY
(
deptID
) REFERENCES dbo.depts
(
ID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
And you have a raw in the employee table that has deptID column with a value that doesn't have a match in the depts. Table, you will get this error message
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_employees_depts". The conflict occurred in database "testing", table "dbo.depts", column 'ID'.
You may think that the foreign key constraint created, not it's not. This error message equivalent to the this error message in SQL 2000
Foreign key 'FK_employees_depts' references invalid column 'deptID' in referencing table 'employees'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
So you need to resolve data conflict in the table before creating the foreign key.
Hope that helps J
- Anonymous
April 13, 2007
Stupore! Amo questo luogo!:)))))))