CODEWITHSUNDEEP
sqlpostgresqlcompiler-errors
Eddie Romanenco
Eddie Romanenco

Reputation: 321

Getting "number of referencing and referenced columns for foreign key disagree" in PostgreSql

I'm creating a few tables in pgAdmin 4, but I keep getting the error it the title for some reason, can you spot why? I don't see any problem and i've looked into other code samples similar to mine which compiled perfectely. It also runs OK in IDEone (http://ideone.com/ZBn2Nr).

thanks!

Create table item
    (iname varchar(30) primary key,
    itype varchar(30));

Create table Cafe
    (license numeric(5,0) primary key,
    cname varchar(30),
    address varchar(30));

Create table Client
    (cid numeric(5,0) primary key,
    name varchar(30),
    phone numeric(9,0));

Create table Likes
    (cid numeric(5,0),
    iname varchar(30),
    primary key(cid,iname),
    foreign key(cid) references Client,
    foreign key(iname) references item);

Create table Sells
    (license numeric(5,0),
    iname varchar(30),
    price float check(price > 0),
    primary key(license,iname),
    foreign key(license) references Cafe,
    foreign key(iname) references item);

Create table Receipt
    (cid numeric(5,0),
    rno numeric(5,0),
    license numeric(5,0),
    rdate date,
    primary key(cid,rno),
    foreign key(cid) references Client,
    foreign key(license) references Cafe);

Create table Buys
    (cid numeric(5,0),
    rno numeric(5,0),
    iname varchar(30),
    amount int check(amount > 0),
    primary key(cid,rno,iname),
    foreign key(cid) references Client,
    foreign key(rno) references Receipt,
    foreign key(iname) references item);

Upvotes: 3

Views: 9815

Answers (1)

Eelke
Eelke

Reputation: 22083

When you do not specify a column list in your references clause it will expand to the primary key of the referenced table.

For example:

foreign key(rno) references Receipt

expands to 

foreign key(rno) references Receipt(cid,rno)

so the number of columns doesn't match

Upvotes: 6

Related Questions