Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] number of referencing and referenced columns forforeign key disagree - Mailing list pgsql-general

From Alexander Farber
Subject Re: [GENERAL] number of referencing and referenced columns forforeign key disagree
Date
Msg-id CAADeyWhmvZcn6AnpU4nc-d9PyVYsq7jd55jv2fz9f-xC0FGrcg@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] number of referencing and referenced columns for foreign key disagree  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
Thank you, Tom! 

Should I have the CHECK in the new table written out again as in -

On Sat, Jul 29, 2017 at 3:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
You have to use the separate-constraint FK syntax:

CREATE TABLE words_payments (
        sid     text        NOT NULL,
        social  integer     NOT NULL ... ,
        foreign key (sid, social) references words_social
);

Or in even more pedantic detail:

        foreign key (sid, social) references words_social (sid, social)

You'd have to use that if (sid, social) were not the PK of words_social
but just some random unique key.


 CREATE TABLE words_payments (
        sid     text        NOT NULL,
        social  integer NOT NULL CHECK (0 < social AND social <= 64), /* should I add this? */
        trans   text      NOT NULL,
        paid    timestamptz NOT NULL,
        price   integer  NOT NULL CHECK (price > 0),
        FOREIGN KEY (sid, social) REFERENCES words_social (sid, social) ON DELETE CASCADE
);

Regards
Alex

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: [GENERAL] number of referencing and referenced columns for foreign key disagree
Next
From: marcelo
Date:
Subject: [GENERAL] Schemas and serials