Foreign Key fails to create

Question

I want a foreign key between 2 tables , so i try it like i always do. Now the issue i'm having is he fails to create , and by the looks of it it fails to create because there is already a key but there isnt.

- Unable to create relationship 
 'FK_tbl_Paramed_RegistratieBehandelingen_Users'.  
  The ALTER TABLE statement conflicted with the 
  FOREIGN KEY constraint "FK_tbl_Paramed_RegistratieBehandelingen_Users". 
  The conflict occurred in database "Nestor_Server", 
  table "dbo.Users", column 'UserID'.

Ive checked if they have the same type , they do(bigint) so don't get why he won't create it

Recitative

Answer 1

It is possible that you have records in RegistratieBehandelingen(Not sure about the table name) which is not present in Users Table.

select * from RegistratieBehandelingen a where UserID IS NULL or
not exists (select 1 from Users b where b.UserID= a.UserID)

Vino

Yeah a null value slipped trought the nets it seems thats why it keeps on failing. Thank you – Recitative

Exactly.. I just had the same problem. To test if this is the case, try creating the foreign key using "NO CHECK". This'll create the key, but won't fall over if the existing data doesn't match the exact foreign key rule you're trying to create. – Quit

Answer 2

This means that you have child data with no matching parent ID.

Run the following to see if you get any results:

SELECT * 
FROM tbl_Paramed_RegistratieBehandelingen r
LEFT JOIN Users u on r.UserID = u.UserID
WHERE u.UserID IS NULL

(changing table and column names where appropriate)

If you get any results then it should show which records contains UserIDs that don't match to Users.

Acciaccatura

Answer 3

After the above query, you may want to delete non existing UserId from table tbl_Paramed_RegistratieBehandelingen or insert them in table Users .

Crowl