Postgresql constraint violated at restore

I have a problem with a constraint that is being violated by some rows when restoring my DB, but has never been violated at usage.

I have 3 tables :

CREATE TABLE IF NOT EXISTS "as" (
  "id" bigserial NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "bs" (
  "id" bigserial NOT NULL,
  "some_id" bigint,
  PRIMARY KEY ("id")
  FOREIGN KEY ("some_id") REFERENCES "some_table" ("id") 
);

CREATE TABLE IF NOT EXISTS "as_bs" (
    "a_id" bigint NOT NULL,
    "b_id" bigint NOT NULL,
    UNIQUE ("a_id", "b_id"),
    FOREIGN KEY ("a_id") REFERENCES "as" ("id"),
    FOREIGN KEY ("b_id") REFERENCES "bs" ("id")
);

some_table here is another table, which I think is not relevant for this problem.

Now what I want is to have a unicity constraint between as.id and bs.some_id through the relation table as_bs. E.g:

INSERT INTO some_table (id) VALUES(1),(2);
INSERT INTO "as" (id) VALUES(1),(2);
INSERT INTO bs (id,some_id) VALUES(1,1),(2,1);
INSERT INTO as_bs (a_id,b_id) VALUES(1,1);
INSERT INTO as_bs (a_id,b_id) VALUES(1,2); -- <<-- Offending row !!!

(Thanks @wildplasser for formatting)

This brings me to the following constraint:

CREATE OR REPLACE FUNCTION check_a_b_some_table_unicity(a_id bigint, b_id bigint)
RETURNS boolean AS
$body$
    BEGIN
        return (SELECT(COUNT(*) = 0)
        FROM as_bs ab
        JOIN bs o1 ON o1.id = ab.b_id
        JOIN bs o2 ON o2.some_id = o1.some_id
        WHERE ab.a_id = $1 AND o2.id = $2);
    END;
$body$
LANGUAGE plpgsql;

ALTER TABLE as_bs ADD CONSTRAINT check_a_b_some_table_unicity CHECK (check_a_b_some_table_unicity(a_id, b_id));

Then my program has lived its life, but when I want to restore a recent backup I got an error because of this constraint.

In my backup, I removed the constraint function and the check and I can restore my backup with no problem. Of course then if I’m trying to reapply the check, I got:

ERROR:  check constraint "check_a_b_some_table_unicity" is violated by some row

So I went to the idea of finding what rows are faulty.

To do that, I joined as and bs to as_bs and grouped by the unicity group (a_id, b_id, some_id):

SELECT a_id, b_id, bs.some_id, COUNT(*) occurrences FROM as_bs
JOIN bs ON as_bs.b_id = bs.id
JOIN as ON as_bs.a_id = as.id
GROUP BY a_id, b_id, bs.some_id HAVING COUNT(*) > 1;

and I got the surprise that it did not return any row…

Now I’m just wondering what’s the problem between

• My constraint is wrong for what I want to do
• My unicity check just above does not check what I want to check

and anyway, I’m also wondering how a constraint that could be registered once can’t be restored now because it was violated.

Answer

CREATE TABLE IF NOT EXISTS "as_bs" (
    "a_id" bigint NOT NULL,
    "b_id" bigint NOT NULL,
    UNIQUE ("a_id", "b_id"),
    FOREIGN KEY ("a_id") REFERENCES "as" ("id"),
    FOREIGN KEY ("b_id") REFERENCES "bs" ("id")
);

---

In your current schema, (as_bs.a_id, as_bs.b_id) is unique Adding dependant columns from as or bs won’t make it more unique.

---

UPDATE:

---

i tmp.sql

CREATE TABLE IF NOT EXISTS "as" (
  "id" bigserial NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "some_table" (
  "id" bigserial NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "bs" (
  "id" bigserial NOT NULL,
  "some_id" bigint,
  PRIMARY KEY ("id"),
  FOREIGN KEY ("some_id") REFERENCES "some_table" ("id")
);

CREATE TABLE IF NOT EXISTS "as_bs" (
    "a_id" bigint NOT NULL,
    "b_id" bigint NOT NULL,
    UNIQUE ("a_id", "b_id"),
    FOREIGN KEY ("a_id") REFERENCES "as" ("id"),
    FOREIGN KEY ("b_id") REFERENCES "bs" ("id")
);

INSERT INTO some_table (id) VALUES(1),(2);
INSERT INTO "as" (id) VALUES(1),(2);
INSERT INTO bs (id,some_id) VALUES(1,1),(2,1);
INSERT INTO as_bs (a_id,b_id) VALUES(1,1);

CREATE OR REPLACE FUNCTION check_a_b_some_table_unicity(a_id bigint, b_id bigint)
RETURNS boolean AS
$body$
    BEGIN
      return NOT EXISTS ( -- Prefer NOT EXISTS to COUNT(*) < 1
        SELECT *
        FROM as_bs ab
        JOIN bs o1 ON o1.id = ab.b_id
        JOIN bs o2 ON o2.some_id = o1.some_id AND o2.id <> o1.id
        WHERE ab.a_id = $1 AND o2.id = $2
        );
    END;
$body$
LANGUAGE plpgsql;

ALTER TABLE as_bs ADD CONSTRAINT check_a_b_some_table_unicity CHECK (check_a_b_some_table_unicity(a_id, b_id));

INSERT INTO as_bs (a_id,b_id) VALUES(1,2); -- <<-- Offending row !!!
                                                                                                                                                                             

---

Output:

---

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 1
CREATE FUNCTION
ALTER TABLE
ERROR:  new row for relation "as_bs" violates check constraint "check_a_b_some_table_unicity"
DETAIL:  Failing row contains (1, 2).