CODEWITHSUNDEEP
sql-serverforeign-keysconstraintsforeign-key-relationship
Nicolas Pierre
Nicolas Pierre

Reputation: 1195

Foreign Key fails to create

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

Upvotes: 26

Views: 25540

Answers (3)

konkass
konkass

Reputation: 1

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

Upvotes: 0

cjk
cjk

Reputation: 46415

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.

Upvotes: 12

praveen
praveen

Reputation: 12271

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)

Upvotes: 55

Related Questions