Thread: Deleting conflicting rows when creating a foreign key

I am doing an ALTER TABLE to create a foreign key, however with some
rows i get:

insert or update on table "name" violates foreign key constraint "name_fkey"

How can I just drop the conflicting rows while doing that?

Igor Katson wrote:
> I am doing an ALTER TABLE to create a foreign key, however with some
> rows i get:
>
> insert or update on table "name" violates foreign key constraint
> "name_fkey"
>
> How can I just drop the conflicting rows while doing that?

You can't automatically. You can do something like:

BEGIN;
SELECT * FROM table1 WHERE col1 NOT IN (SELECT id from table2);
-- check results are as you'd expect
DELETE FROM table1 WHERE col1 NOT IN (SELECT id from table2);
ALTER TABLE table1 ADD CONSTRAINT ... FOREIGN KEY ...
COMMIT;

--
  Richard Huxton
  Archonet Ltd

Richard Huxton wrote:

> DELETE FROM table1 WHERE col1 NOT IN (SELECT id from table2);

Just as a side note: If you have a large number of missing IDs and don't
want to wait a long time, you may be better off with something like
(untested, but I think it's right - TEST FIRST):

SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
WHERE t2.id IS NULL;

-- check that the rows to be deleted are OK

DELETE FROM table1
USING table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
WHERE table1.id = t1.id AND t2.id IS NULL;

(by the way, being able to specify an explicit join method in a DELETE
... USING or update ... USING would be *great*).

--
Craig Ringer

Craig Ringer wrote:
> Richard Huxton wrote:
>
>
>> DELETE FROM table1 WHERE col1 NOT IN (SELECT id from table2);
>>
>
> Just as a side note: If you have a large number of missing IDs and don't
> want to wait a long time, you may be better off with something like
> (untested, but I think it's right - TEST FIRST):
>
> SELECT *
> FROM table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
> WHERE t2.id IS NULL;
>
> -- check that the rows to be deleted are OK
>
> DELETE FROM table1
> USING table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
> WHERE table1.id = t1.id AND t2.id IS NULL;
>
> (by the way, being able to specify an explicit join method in a DELETE
> ... USING or update ... USING would be *great*).
>
> --
> Craig Ringer
>
Thanks for the advice, Craig, I didn't know about that syntax before.