Обсуждение: [GENERAL] number of referencing and referenced columns for foreign key disagree

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

[GENERAL] number of referencing and referenced columns for foreign key disagree

От
Alexander Farber
Дата:
Good afternoon, 

in 9.5.7 I have the following 2 tables -

CREATE TABLE words_users (
        uid     SERIAL PRIMARY KEY,

        created timestamptz NOT NULL,
        visited timestamptz NOT NULL,
        ip      inet        NOT NULL,

        fcm     text,
        apns    text,
        sns     text,
        motto   text,

        vip_until   timestamptz,
        grand_until timestamptz,

        banned_until timestamptz,
        banned_reason text CHECK (LENGTH(banned_reason) > 0),

        elo     integer NOT NULL CHECK (elo >= 0),
        medals  integer NOT NULL CHECK (medals >= 0),
        coins   integer NOT NULL
);

CREATE TABLE words_social (
        sid     text     NOT NULL,
        social  integer  NOT NULL CHECK (0 < social AND social <= 64),
        given   text     NOT NULL CHECK (given ~ '\S'),
        family  text,
        photo   text CHECK (photo ~* '^https?://...'),
        lat     float,
        lng     float,
        stamp   integer  NOT NULL,

        uid     integer     NOT NULL REFERENCES words_users ON DELETE CASCADE,
        PRIMARY KEY(sid, social)
);

And then I am trying to add another table, which should reference the (sid, social) pair -

CREATE TABLE words_payments (
        sid     text        NOT NULL REFERENCES words_social ON DELETE CASCADE,
        social  integer     NOT NULL CHECK (0 < social AND social <= 64) REFERENCES words_social ON DELETE CASCADE,
        trans   text        NOT NULL,
        paid    timestamptz NOT NULL,
        price   integer     NOT NULL
);

Unfortunately, I get the error:

ERROR:  42830: number of referencing and referenced columns for foreign key disagree
LOCATION:  ATAddForeignKeyConstraint, tablecmds.c:6345

How to refer to the (sid, social) FKs properly please?

The background is that social is one of the predefined (by me) constants:
    public static final int UNKNOWN                     = 0;
    public static final int GOOGLE                      = 1;
    public static final int APPLE                       = 2;
    public static final int ODNOKLASSNIKI               = 4;
    public static final int MAILRU                      = 8;
    public static final int VKONTAKTE                   = 16;
    public static final int FACEBOOK                    = 32;
    public static final int AMAZON                      = 64;

And the sid is a "user id" used in the social network (for example Facebook user id).

Thank you
Alex

Re: [GENERAL] number of referencing and referenced columns for foreign key disagree

От
Tom Lane
Дата:
Alexander Farber <alexander.farber@gmail.com> writes:
> Unfortunately, I get the error:
> ERROR:  42830: number of referencing and referenced columns for foreign key
> disagree

> How to refer to the (sid, social) FKs properly please?

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.

            regards, tom lane

Re: [GENERAL] number of referencing and referenced columns forforeign key disagree

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