Purge invalid data before creating a Foreign Key (FK)

Asked

Viewed 49 times

0

I’m working with a database bequest where there are 3 tables: Tabela A, Tabela B and Tabela C which stores the relationship between Tables A and B, however when the bank was created the Foreign Keys (FK) were not created now it is necessary to create them, but as there was no FK to Table C (which stores the relationship) this through joke since records were removed from Table A, Table B without FK restriction. How it would purge the data of Table C that are not present in A and B to be able to create the necessary FK (Agora it is essential)?

Graphic example of the current situation:

inserir a descrição da imagem aqui

in green the table OK, with the passage of time the records of Tables A and B were being removed (Red Flag) and Table C already joke.

Ambience:
Database: SQL Server 2008

sql sql-server

1 answer

2


Just make a DELETE in Tabela C, checking whether the id that are being used in the table C no longer exists in the A or in the B.

DELETE
FROM
    Tabela C 
WHERE
    idTabelaA NOT IN (SELECT id FROM TabelaA) OR 
    idTabelaB NOT IN (SELECT id FROM TabelaB)

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.