number of referencing and referenced columns for foreign key disagree

Started by Alexander Farberover 8 years ago3 messagesgeneral
Jump to latest
#1Alexander Farber
alexander.farber@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Alexander Farber (#1)
Re: number of referencing and referenced columns for foreign key disagree

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#3Alexander Farber
alexander.farber@gmail.com
In reply to: Tom Lane (#2)
Re: number of referencing and referenced columns for foreign key disagree

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