problem creating a simple relationship in SQL Server 2005

Question

I am using the database diagram to simply drag one column in a table to another to associate them and then trying to save it. i have done this a million times in the past with no problems. Both of the data types are the same, uniqueidentifier.

Here is the error I get:

'Customer ' table saved successfully
'CustomerOrder ' table
- Unable to create relationship 'FK_CustomerOrder_Customer'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_CustomerOrder_Customer". The conflict occurred in database "mydatabase", table "Customer", column 'CustomerID'.

Not sure how to trouble shoot this.

Answer 1

Are there customer orders with customer id's that don't exist in the customer table?

Answer 2

It means that there's a CustomerID in the CustomerOrder which can not be found in the Customer table.

Run this query inside SQL Server Management Studio separately:

SELECT *
FROM CustomerOrder co
WHERE NOT EXISTS (SELECT * FROM Customer c WHERE c.CustomerID = co.CustomerID)

and that should tell you what the "bad" Customer Order records are.