1

I have 2 tables. I am trying to create a FORIEGN KEY. Here is my first table:

CREATE TABLE bills(
 id   serial,
 name  varchar(100),
 payment  decimal(12, 2),
 payoff  decimal(12, 2),
 type  varchar(25)
)

When I try to create a second table:

CREATE TABLE pay_dates(
 id serial,
 bill_id integer REFERENCES bills(id),
 due_date date,
 pay_date date,
 paid  boolean
)

I get this error:

ERROR: there is no unique constraint matching given keys for referenced table "bills".

What am I doing wrong?

jhamm
  • 24,124
  • 39
  • 105
  • 179

2 Answers2

2

The referenced column has to be unique. You should declare id as primary key:

CREATE TABLE bills(
 id   serial primary key,
...

Primary key (or unique) constraint must be declared explicit. Type serial ensures only that the default value is the next value from a sequence. Example:

create table test (val1 serial, val2 serial);
insert into test (val2) values (1), (2), (default), (3), (4), (default);
select * from test;

 val1 | val2
------+------
    1 |    1
    2 |    2
    3 |    1
    4 |    3
    5 |    4
    6 |    2
(6 rows)
klin
  • 112,967
  • 15
  • 204
  • 232
  • @jhamm No, `SERIAL` does not mean unique. It means: “please create a `SEQUENCE` object in my database, and use numbers from that sequence as the default value in new rows of this table”. See [the doc](http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL) and read carefully the expanded SQL. Marking a column as `UNIQUE` (or `PRIMARY KEY`) says “please create an index on this column, and use that index to always check that new or modified rows have no duplicate values already stored on other rows in this column”. SERIAL and UNIQUE/PRIMARY KEY do different jobs. – Basil Bourque Jun 21 '15 at 21:30
1

The parent table bills (id) must have either unique or primary key constraint.

What is causing ERROR: there is no unique constraint matching given keys for referenced table?

Community
  • 1
  • 1
KDP
  • 1,481
  • 7
  • 13