0

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.

  • The data exists between the three tables. The change was to drop a column from the lookup table. In doing so the lookup table had to be dropped and recreated. That's when the problems started. Wouldn't the error have indicated a data-related issue? – Gary Nov 18 '14 at 16:56
  • It does indicate a data related issue. ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Relationship26". You cannot create the Foreign Key, because it conflicts with the principal of the foreign key... – Mark Sinkinson Nov 18 '14 at 17:01
  • Why did you drop and recreate the table? Couldn't you just alter the table and drop the column? And is the Users.UserID an IDENTITY column? How did you do the drop and recreate? (how the data were saved) – ypercubeᵀᴹ Nov 18 '14 at 17:25
  • 1
    This is a development database (thankfully) in a small shop...I don't "think" there are any operations going on. One of the previous 25 attempts was to drop the user table with the plan of repopulating the data from production. When I use @MarkSinkinson suggestion (after bringing the data down from production) I get three rows in the tblUserRoles table that are orphaned. After fixing that (and having to modify a trigger), I found one in the roles. Fixing those I was able to add the constraints. They are named: tblUsers_tblUserRoles and tblRoles_tblUserRoles. Mr. Sinkinson, I owe you a beer! – Gary Nov 18 '14 at 17:37
  • @ypercube, I dropped the column on the lookup table, not the table. Many desperation attempts later I dropped the users table. What I have learned is the the relationship does not appear in the results if there are data violations, which there apparently were. This is what through me off. – Gary Nov 18 '14 at 17:40
2

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?!

  • As I already indicated, this has almost nothing to do with the issue. a,b) All tables were involved in a foreign key relationship. c) That's how many times I've attempted a solution for this! I have a naming standard in place. When I can generate the relationship I'll give it a meaningful name...however it might be snarky at this point! – Gary Nov 18 '14 at 17:01
  • 1
    @Gary So what were solutions 1 through 25? On the off chance that you do SELECT UserID FROM dbo.tblUserRoles EXCEPT SELECT UserID FROM dbo.tblUsers;, what is returned? – Mark Sinkinson Nov 18 '14 at 17:02
  • 1
    @Gary, you say that "All tables were involved in a foreign key relationship." But I assume those foreign keys were dropped. In the mean time between dropping them and trying to recreate them, the tables may have been modified (rows inserted, updated, deleted) and the constraints are now violated and the ALTER TABLE fails. – ypercubeᵀᴹ Nov 18 '14 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.