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  wrote:
>
> > On 11 Mar 2017, at 10:41, Alexander Farber 
> 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!

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

[Alban Hertroys]

> On 11 Mar 2017, at 10:41, Alexander Farber  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

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

[Francisco Olarte]

Alexander:

On Sat, Mar 11, 2017 at 10:41 AM, Alexander Farber
 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

[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