[GENERAL] CHECK for 2 FKs to be non equal

Поиск
Список
Период
Сортировка
От Alexander Farber
Тема [GENERAL] CHECK for 2 FKs to be non equal
Дата
Msg-id CAADeyWh_ioA+_8YnAWCA7nvDTWdi72NRPP8FSHxivmnSt5LmQw@mail.gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] CHECK for 2 FKs to be non equal
Re: [GENERAL] CHECK for 2 FKs to be non equal
Список 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





В списке pgsql-general по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: [GENERAL] Unable to start postgresql
Следующее
От: Francisco Olarte
Дата:
Сообщение: Re: [GENERAL] CHECK for 2 FKs to be non equal