The ALTER TABLE statement conflicted with the FOREIGN KEY constrain

Question

I had a specific relational database with different tables. what I wanted to do is actually to copy two tables from different DB located on different server to my database using export feature in Microsoft SQL Server Management Studio 2017. The new tables have been imported to my current database. However, the problem is that the tables that have been exported from the other database into mine don't have primary and foreign keys. So what I did, I deleted the old tables in my current database and now working on adding the primary and foreign keys to the freshly copied one.

The issue is that I was able to create the primary keys without any problems however, when I tried to create the foreign key to the second table to link it to the first table I Got this error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constrain

I know the reason of the error that I have unmatched number of items in my first and second tables. I ran this query:

select UUT_RESULT from STEP_RESULT
WHERE UUT_RESULT NOT IN
(SELECT ID from UUT_RESULT)

I was able to see that there is 883 entries in my second table doesn't have match in the first table.

How can I solve this problem? I can't clean the tables because they have 15 million entries so is there a way that I can delete these 883 from my second table and then I can match the two tables?

Answer 1

Try this:

BEGIN TRANSACTION

DELETE a
FROM STEP_RESULT a
WHERE NOT EXISTS (
        SELECT *
        FROM UUT_RESULT
        WHERE ID = a.UUT_RESULT
        )
--commit    --verify rowcount before commit