CHECK for 2 FKs to be non equal
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
Alexander:
On Sat, Mar 11, 2017 at 10:41 AM, Alexander Farber
<alexander.farber@gmail.com> wrote:
uid integer NOT NULL REFERENCES words_users(uid) CHECK (uid <>
author) ON DELETE CASCADE,
Maybe a stupid question, but have you tried "refereces.. on delete .. check"?
I mean, the manual for create table says:
column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
...And a little down
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL |
MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
<<<<
So ON DELETE is an optional part of a reference constraint, not a
constraint per se, and it is being parsed as "references..." ( correct
constraint) + "check..." (correct constraint) + "On delete.." (WTF is
this ), on delete after references should be parsed as a single big
constraint.
What am I doing wrong please?
Not RTFM ? ( if I'm right, or not understanding it )
Francisco Olarte.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 11 Mar 2017, at 10:41, Alexander Farber <alexander.farber@gmail.com> wrote:
uid integer NOT NULL REFERENCES words_users(uid) CHECK (uid <> author) ON DELETE CASCADE,
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 …
You put your CHECK constraint definition smack in the middle of the FK constraint definition, which starts with REFERENCES and ends with the delete CASCADE.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thank you Alban and Francisco -
On Sat, Mar 11, 2017 at 11:52 AM, Alban Hertroys <haramrae@gmail.com> wrote:
On 11 Mar 2017, at 10:41, Alexander Farber <alexander.farber@gmail.com>
wrote:
uid integer NOT NULL REFERENCES words_users(uid) CHECK (uid <>
author) ON DELETE CASCADE,
You put your CHECK constraint definition smack in the middle of the FK
constraint definition, which starts with REFERENCES and ends with the
delete CASCADE.
you are both correct!