Misleading error message when you create foreign key constraint

  • Article

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