Problem adding foreign key in simple setup

Question

I have two tables

Users
Users_Role

I decided to try to add a foreign key as to my understanding it will let me cascade the delete procedure when removing a user from Users (as well as enforce integrity).

So via Management Studio I right clicked my Users_Role table, Design, then went into Relationships. I added a new relationship between the two tables as such:

Foreign Key Base Table: Users_Role
Foreign Key Columns: UserID
Primary/Unique Key Base: Users
Primary/Unique Key columns: ID

When I then try to save, I get the following error:

'Users' table saved successfully
'Users_Role' table
- Unable to create relationship 'FK_Users_Role_Users'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Users_Role_Users". The conflict occurred in database "db", table "dbo.Users", column 'ID'.

I'm not sure what's going on. Adds the relationship to Users, but not Users_Role? The only thing special about Users_Role is that my primary key consists of two columns, UserID and Role (the only two columns in the table, if that matters). Otherwise, nothing special.

Answer 1

This error means that in your current database, you have entries in the "Users_Role" table which have a "UserID" value that is not present in the Users table as ID.

You first need to find those "rogue" rows and either update or delete them, before you can create the foreign key to enforce referential integrity and avoid such problems in the future.

You can find those by issuing this command:

  SELECT * FROM Users_Role
  WHERE UserID NOT IN 
        (SELECT DISTINCT ID FROM Users)