Today I learned how to find and filter out rows from a query that violate a foreign key constraint on the table I am inserting data into. Now I can insert data without using INSERT IGNORE
Given we are inserting data from a legacy system table with no constraints into a new system table with constraints.
Let’s assume the new table has a constraint on the columns name and zip_codesuch that only one store can have the same name for the same zip code.
Stores
id
name
zip_codeThe below query INSERT query allows us to only select valid data that will not violate the constraint in place in the new system.
This same strategy can be used to find violating rows by editing the SELECT query to read WHERE NOT IN, useful if you are writing validation tests.
INSERT INTO new.stores (
name,
zip_code
)
SELECT name, zip_code
FROM legacy.stores s
WHERE s.id IN (
SELECT id
FROM (
SELECT
MIN(t.id) as id,
name,
zip_code
FROM legacy.stores s
GROUP BY s.name, s.zip_code
) t
)
);