Sql-server – Foreign keys can’t be added because they exist but they don’t exist!

foreign keyreferential-integritysql-server-2005

I have a conundrum. I am re-engineering a sqlserver 2005 database, and in the course of doing so have attempted to correct a simple lookup table flaw between tblUsers, tblUserRoles, and tblRoles (tblUserRoles had its own pk field).

In trying to make this change, foreign key relationships where dropped and recreated, except they weren't.

Whenever I try to:

ALTER TABLE [dbo].[tblUserRoles] ADD CONSTRAINT [Relationship26] FOREIGN KEY ([UserID]) REFERENCES [dbo].[tblUsers] ([UserID])
go
ALTER TABLE [dbo].[tblUserRoles] ADD CONSTRAINT [Relationship27] FOREIGN KEY ([RoleID]) REFERENCES [dbo].[tblRoles] ([RoleID])
go

I get:

Msg 547, Level 16, State 0, Line 230

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Relationship26". The conflict occurred in database "AhelpDev", table "dbo.tblUsers", column 'UserID'.
Msg 547, Level 16, State 0, Line 232
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Relationship27". The conflict occurred in database "AhelpDev", table "dbo.tblRoles", column 'RoleID'.

But when I:

ALTER TABLE [dbo].[tblUserRoles] Drop CONSTRAINT [Relationship26]
go
ALTER TABLE [dbo].[tblUserRoles] Drop CONSTRAINT [Relationship27]
go

I get:

Msg 3728, Level 16, State 1, Line 236
'Relationship26' is not a constraint.
Msg 3727, Level 16, State 0, Line 236
Could not drop constraint. See previous errors.
Msg 3728, Level 16, State 1, Line 238
'Relationship27' is not a constraint.
Msg 3727, Level 16, State 0, Line 238
Could not drop constraint. See previous errors.

When I run:

select * from  sys.Foreign_keys where name in ('relationship26', 'relationship27')

It returns no records. I'm stumped, I turn to you for help!

I've even attempted to copy the database to see if I could leave it behind…I can't!

I look forward to your answers.

Best Answer

You cannot add the constraints to tblUserRoles because the data within this table already violates the constraints that you are trying to add.

a) Double check that all UserID values in tblUserRoles reference UserID values in tblUsers

b) Do the same as the above for tblRoles and RoleID.

c) As I imagine both of the above will return records that are not in the referenced table, you'll need to clean up your data before adding the Foreign Keys

d) Name you foreign keys something useful! If I came to work on your database, how am I supposed to know what Relationship26 means?!

Related Question