From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | number of referencing and referenced columns for foreign key disagree |
Date: | 2017-07-29 12:13:59 |
Message-ID: | CAADeyWhPqTM8OAY5pMe-yY0LFr+vYginMLueyCJLmSrdCyu3Gw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-07-29 13:41:41 | Re: number of referencing and referenced columns for foreign key disagree |
Previous Message | Jerome Wagner | 2017-07-29 10:03:35 | tzdata timezone boundaries |