insert several row in order to satisfy a constraint

Question

hello,

I have two table: deck(id) and card(deck,color,value)

deck have those constraints:

• CHECK (fifty_two_cards_deck(id))

• PRIMARY KEY (id)

  CREATE FUNCTION fifty_two_cards_deck(deck integer) RETURNS boolean LANGUAGE sql STABLE STRICT AS $_$ SELECT COUNT(*)=52 FROM card WHERE deck=$1 $_$;

and card have those constraints:

• FOREIGN KEY (deck) REFERENCES deck(id)
• PRIMARY KEY (deck, color, value)

How can I insert a new deck?

I tried this:

begin transaction;
INSERT INTO "public"."deck" ("id") VALUES (nextval('deck_id_seq'::regclass));
INSERT INTO "public"."card" ("deck", "color", "value") VALUES ('1', enum_first(null::Suit), enum_first(null::Symbol));

end transaction

(i had edit fifty_two_cards_deck to be a one_card_deck for testing purpose) but I got this error:

SQL error:

ERROR: new row for relation "deck" violates check constraint "fifty_two_cards_deck"

In statement: begin transaction; INSERT INTO "public"."deck" ("id") VALUES (nextval('deck_id_seq'::regclass)); INSERT INTO "public"."card" ("deck", "color", "value") VALUES ('1', enum_first(null::Suit), enum_first(null::Symbol));

end transaction

How can I solve this without removing the constraints?

EDIT: solution

thx to Magnus Hagander I got it working like this (after setting the foreign key deferrable):

begin transaction;

SET CONSTRAINTS ALL DEFERRED;

INSERT INTO "public"."deck-card" ("deck", "position", "color", "value") VALUES (1, 0, enum_first(null::suit), enum_first(null::Symbol));
INSERT INTO "public"."deck" ("id") VALUES (1);

end transaction

Answer 1

It might work if you make the FOREIGN KEY with DEFERRABLE, and then set it to DEFERRED. Then you insert into the "card" table first, and then into "deck". Check constraints execute at the time of insert (thus, well before the entries in "card" exist), and cannot be deferred to transaction end.

But that's not actually going to work around the fact that your constraint is broken and should be removed ;) That CHECK constraint will only check rows going into "deck". But once the row has been inserted there, you will still be able to add more rows to, or delete rows from, the "card" table and the CHECK constraint will not complain - until the next time you try to modify "deck".