SQL Compact (CE) problem with creating foreign key

Question

I'm trying to create a foreign key in a SQL Compact database but I keep getting the error message "A foreign key value cannot be inserted because a corresponding primary key value does not exist."

TableA is referencing TableB already and I'm trying to create a reference from TableC using the same primary key in TableB. Since I already have a reference to TableB there has to be a valid primary key in that table.

I've double checked and tripple checked the keys and datatypes of all field so I'm stumped.

I've tried to use both the gui option in Visual Studio 2008 and using a SQL command.

Answer 1

This is because of "bad data" you have in the tables. Check if you have all corresponding values in the primary table.

DBMS checks the referential integrity for ensuring the "correctness" of data within database.

example: If you have column StatusId in the Table1 with the values from 1 to 10 and column StatusId in the Table2 with the values from 1 to 11 you cannot use Table1 as a parent because there is no corresponding value (11) you have already in Table2.