CODEWITHSUNDEEP
postgresql
samol
samol

Reputation: 20640

Postgres: violates foreign key constraint

I used a COPY command to load a bunch of data in the database and then created the primary keys and foreign keys afterwards.

When I created one of the foreign keys, I got the error message: violates foreign key constraint "some_fkey" . Is there a way that I can raise the log level so that I can see on which row this violation happened so that I can go back and debug/check the data?

Upvotes: 0

Views: 7507

Answers (1)

Milen A. Radev
Milen A. Radev

Reputation: 62673

A workaround:

Suppose your tables look like this:

CREATE TABLE a (a_id INTEGER PRIMARY KEY, x TEXT);
CREATE TABLE b (b_id INTEGER PRIMARY KEY, a_id INTEGER, x TEXT);

And the creation of the following foreign key gives you the error:

ALTER TABLE b ADD CONSTRAINT a_a_id_fk FOREIGN KEY (a_id) REFERENCES a (a_id) ON UPDATE CASCADE ON DELETE RESTRICT;

You could join the two tables to get the problematic rows:

SELECT b_id, a_id FROM b LEFT JOIN a USING (a_id) WHERE a.a_id IS NULL;

Let's see!

Sample data:

INSERT INTO a VALUES (1, 'a'), (2, 'b'), (3, 'c'); 
INSERT INTO b VALUES (1, 1, 'x'), (2, 3, 'y'), (3, 1000, 'z');

And the result of the query above:

 b_id | a_id
------+------
    3 | 1000
(1 row)

Upvotes: 3

Related Questions