Re: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Hi Adrian,

On Sat, May 5, 2018 at 7:03 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 05/05/2018 07:49 AM, Alexander Farber wrote:

http://sqlfiddle.com/#!17/7e929/13 

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? :-)

Regards
Alex