Обсуждение: [GENERAL] CHECK for 2 FKs to be non equal

Поиск
Список
Период
Сортировка

[GENERAL] CHECK for 2 FKs to be non equal

От
Alexander Farber
Дата:
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





Re: [GENERAL] CHECK for 2 FKs to be non equal

От
Francisco Olarte
Дата:
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.

Re: [GENERAL] CHECK for 2 FKs to be non equal

От
Alban Hertroys
Дата:
> 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
REFERENCESand 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.

Re: [GENERAL] CHECK for 2 FKs to be non equal

От
Alexander Farber
Дата:
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!