Re: Deleting conflicting rows when creating a foreign key - Mailing list pgsql-general

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