From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | CHECK for 2 FKs to be non equal |
Date: | 2017-03-11 09:41:27 |
Message-ID: | CAADeyWh_ioA+_8YnAWCA7nvDTWdi72NRPP8FSHxivmnSt5LmQw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Good morning,
I am trying to add a table holding player reviews of each other:
words=> CREATE TABLE words_reviews (
uid integer NOT NULL REFERENCES words_users(uid) CHECK (uid <>
author) ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice boolean NOT NULL,
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);
but get syntax error in 9.5:
ERROR: syntax error at or near "ON"
LINE 2: ...REFERENCES words_users(uid) CHECK (uid <> author) ON DELETE ...
^
My intention is to forbid users to rate themselves by the CHECK (uid <>
author).
What am I doing wrong please?
Regards
Alex
P.S. I apologize if GMail misformats my message... Here is the words_users
table:
words=> \d words_users
Table "public.words_users"
Column | Type |
Modifiers
---------------+--------------------------+-----------------------------------------------------------
uid | integer | not null default
nextval('words_users_uid_seq'::regclass)
created | timestamp with time zone | not null
visited | timestamp with time zone | not null
ip | inet | not null
fcm | character varying(255) |
apns | character varying(255) |
vip_until | timestamp with time zone |
grand_until | timestamp with time zone |
banned_until | timestamp with time zone |
banned_reason | character varying(255) |
win | integer | not null
loss | integer | not null
draw | integer | not null
elo | integer | not null
medals | integer | not null
green | integer | not null
red | integer | not null
coins | integer | not null
Indexes:
"words_users_pkey" PRIMARY KEY, btree (uid)
Check constraints:
"words_users_banned_reason_check" CHECK (length(banned_reason::text) >
0)
"words_users_draw_check" CHECK (draw >= 0)
"words_users_elo_check" CHECK (elo >= 0)
"words_users_green_check" CHECK (green >= 0)
"words_users_loss_check" CHECK (loss >= 0)
"words_users_medals_check" CHECK (medals >= 0)
"words_users_red_check" CHECK (red >= 0)
"words_users_win_check" CHECK (win >= 0)
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY
(player1) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY
(player2) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_notes" CONSTRAINT "words_notes_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2017-03-11 10:45:44 | Re: CHECK for 2 FKs to be non equal |
Previous Message | Michael Paquier | 2017-03-10 22:47:58 | Re: Unable to start postgresql |