On 05/05/2018 10:41 AM, Alexander Farber wrote:
Hi Adrian,
There is a two-player word game:
CREATE TABLE players (
uid SERIAL PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE games (
gid SERIAL PRIMARY KEY,
player1 integer NOT NULL REFERENCES players ON DELETE CASCADE,
player2 integer NOT NULL REFERENCES players ON DELETE CASCADE,
hand1 char[7] NOT NULL,
hand2 char[7] NOT NULL
);
CREATE TABLE stats (
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
single_q_left INTEGER NOT NULL DEFAULT 0
);
The uid column in the stats table has neither a unique or exclusion
constraint on it.
The uid column in stats refers to uid column in players, where it is
PRIMARY KEY, why doesn't PostgreSQL "see" that? :-)
Because the potential conflict it may have to deal with is the INSERT to
the table stats, not the table players. As it stands now the uid column
can have duplicate values so there is no way for Postgres to know what
row is in conflict.
To have ON CONFLICT work you need to trip the following:
https://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT
"The optional ON CONFLICT clause specifies an alternative action to
raising a unique violation or exclusion constraint violation error."
Regards
Alex
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
