Postgresql: How can I verify a constraint after several update queries?

Question

I’m building a database with postgresql.

I have 3 tables linked together by a many to many relation. The join table uiComponent_cat_subcat defines a unique constraint with subcategoryId, categoryId and position (red on the picture).

In my workflow I must make 2 queries update in a row. The first one breaks the constraint whereas the second one sets it back.

I would like this constraint to be verified after the 2 queries.

ALTER TABLE "uiComponent_cat_subcat" ALTER CONSTRAINT "unique_constraint_categoryId_subcategoryId_position" DEFERRABLE INITIALLY DEFERRED;

I tried to use the DEFERRABLE property on the constraint and to wrap my queries inside one transaction. However this can only be applied on constraints set to foreign keys.

How can I verify the unique constraint after the two update queries were made?

Edit: Here are the object definitions:

1 - CREATE TABLE

CREATE TABLE IF NOT EXISTS "uiComponent" 
(
  "id"           uuid NOT NULL,
  "name"         varchar(255),
  "preview"      json,
  "symbolID"     varchar(255),
  "libraryID"    varchar(255),
  "type"         "public"."enum_uiComponent_type" DEFAULT 'basic',
  "content"      json,
  "created_at"   timestamp WITH time ZONE NOT NULL DEFAULT now(),
  "updated_at"   timestamp WITH time ZONE NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "category" 
(
  "id"            uuid NOT NULL,
  "name"          varchar(255),
  "immuable"      boolean,
  "description"   varchar(255),
  "section"       "public"."enum_category_section" NOT NULL DEFAULT 'Components',
  "created_at"    timestamp WITH time ZONE NOT NULL DEFAULT now(),
  "updated_at"    timestamp WITH time ZONE NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "subcategory" 
(
  "id"           uuid NOT NULL,
  "name"         varchar(255),
  "position"     integer NOT NULL,
  "created_at"   timestamp WITH time ZONE NOT NULL DEFAULT now(),
  "updated_at"   timestamp WITH time ZONE NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "uiComponent_cat_subcat" 
(
  "id"              uuid,
  "uiComponentId"   uuid NOT NULL REFERENCES "uiComponent" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "categoryId"      uuid NOT NULL REFERENCES "category" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "subcategoryId"   uuid REFERENCES "subcategory" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
  "position"        integer NOT NULL,
  PRIMARY KEY ("id")
);

2 - CREATE CONSTRAINT

ALTER TABLE "uiComponent_cat_subcat" 
   ADD CONSTRAINT "unique_constraint_categoryId_subcategoryId_position" UNIQUE ("categoryId", "subcategoryId", "position") 
   DEFERRABLE INITIALLY DEFERRED;

3 - UPDATE QUERIES

START TRANSACTION;

UPDATE "uiComponent_cat_subcat" 
  SET "position"="position"+ 1 
WHERE "position" BETWEEN 1 AND 1 
AND "categoryId" = '10e4621e-f52d-4fe2-a408-139841718fd5' 
AND "subcategoryId" = 'a7770326-35be-45ae-ba26-4635cfb6f4dc';

UPDATE "uiComponent_cat_subcat" 
  SET "position"=1 
WHERE "id" = '50022f87-8fe9-4f21-a622-d5f51c16d9fc'

COMMIT;

The problem is solved but I don’t know why Does wrapping queries in a transaction allows me to check the constraint after the commit.

I don't see the problem. With deferred foreign keys you can update `uiComponent_cat_subcat ` after you have changed the other tables. Just don't use cascading updates. Also, if you are updating primary keys, you are doing something wrong. But you better explain in more detail by showing the SQL statements you want to run and the table definitions. — Laurenz Albe, May 22 '18 at 11:19
Why is componentId not in uiComponent_cat_subcat's PK ? And Category seems functionally dependent on subcategory (and position is redundant, but possibly part of another candidate key) — joop, May 22 '18 at 12:29
@LaurenzAlbe Thanks. How can I increment multiple rows and update one row in same query? PS: I wrote all the queries in response of this question — Moreaux Antoine, May 22 '18 at 13:36
@joop A component can be in several couple of category/subcategory and also have different position in each couple. Plus, what do you mean by “Why is `componentId` not in `uiComponent_cat_subcat`'s PK”? `uiComponent_cat_subcat` can have several `uiComponentId`. — Moreaux Antoine, May 22 '18 at 13:41

score 1 · Answer 1 · edited May 22 '18 at 13:30

1 - CREATE TABLE

CREATE TABLE IF NOT EXISTS "uiComponent" 
(
  "id"           uuid NOT NULL,
  "name"         varchar(255),
  "preview"      json,
  "symbolID"     varchar(255),
  "libraryID"    varchar(255),
  "type"         "public"."enum_uiComponent_type" DEFAULT 'basic',
  "content"      json,
  "created_at"   timestamp WITH time ZONE NOT NULL DEFAULT now(),
  "updated_at"   timestamp WITH time ZONE NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "category" 
(
  "id"            uuid NOT NULL,
  "name"          varchar(255),
  "immuable"      boolean,
  "description"   varchar(255),
  "section"       "public"."enum_category_section" NOT NULL DEFAULT 'Components',
  "created_at"    timestamp WITH time ZONE NOT NULL DEFAULT now(),
  "updated_at"    timestamp WITH time ZONE NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "subcategory" 
(
  "id"           uuid NOT NULL,
  "name"         varchar(255),
  "position"     integer NOT NULL,
  "created_at"   timestamp WITH time ZONE NOT NULL DEFAULT now(),
  "updated_at"   timestamp WITH time ZONE NOT NULL DEFAULT now(),
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "uiComponent_cat_subcat" 
(
  "id"              uuid,
  "uiComponentId"   uuid NOT NULL REFERENCES "uiComponent" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "categoryId"      uuid NOT NULL REFERENCES "category" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "subcategoryId"   uuid REFERENCES "subcategory" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
  "position"        integer NOT NULL,
  PRIMARY KEY ("id")
);

2 - CREATE CONSTRAINT

ALTER TABLE "uiComponent_cat_subcat" 
   ADD CONSTRAINT "unique_constraint_categoryId_subcategoryId_position" UNIQUE ("categoryId", "subcategoryId", "position") 
   DEFERRABLE INITIALLY DEFERRED;

3 - UPDATE QUERIES

START TRANSACTION;

UPDATE "uiComponent_cat_subcat" 
  SET "position"="position"+ 1 
WHERE "position" BETWEEN 1 AND 1 
AND "categoryId" = '10e4621e-f52d-4fe2-a408-139841718fd5' 
AND "subcategoryId" = 'a7770326-35be-45ae-ba26-4635cfb6f4dc';

UPDATE "uiComponent_cat_subcat" 
  SET "position"=1 
WHERE "id" = '50022f87-8fe9-4f21-a622-d5f51c16d9fc'

COMMIT;

The problem is solved but I don’t know why Does wrapping queries in a transaction allows me to check the constraint after the commit.

`INITIALLY DEFERRED;` is the key reason why it now works. – May 22 '18 at 13:28 — , May 22 '18 at 13:28
@a_horse_with_no_name Thank you very much – Moreaux Antoine May 22 '18 at 13:43 — Moreaux Antoine, May 22 '18 at 13:43

Postgresql: How can I verify a constraint after several update queries?

1 Answers1