0

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?

  • So, to be clear, you want to delete from the child table any row where the foreign key cannot be found on the parent table? – Scott Hodgin May 9 at 14:06
  • exactly! I copied the two tables from live database. Copied the parent table first(UUT_RESULT) then copied (STEP_RESULT). it seems that some entries have been added to the child table by the time I copied it which doesn't have primary key in the parent table. – Nano May 9 at 14:10
0

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
  • when I ran your query I got the same number: 883 entries. Should I just send a new query with --commit? – Nano May 9 at 14:21
  • if you still have the query window up from the original run, just highlight the commit and click execute to commit the transaction. – Scott Hodgin May 9 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.