PostgreSQL constraint error

Question

i m having this error:

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

This is my code to make a foreign key:

ALTER TABLE put_25_vlak_1_spoor
ADD FOREIGN KEY (spoor,vulling)
REFERENCES vulling(spoor,vulling);

The tables are made this way:

CREATE TABLE put_25_vlak_1_spoor
(
  id serial NOT NULL,
  geometry_spoor geometry(MultiPolygon,28992),
  spoor integer NOT NULL,
  put integer,
  vlak integer,
  vulling integer NOT NULL,
  CONSTRAINT put_25_vlak_1_spoor_pkey PRIMARY KEY (spoor, vulling)
)

CREATE TABLE vulling
(
  vlak smallint,
  textuur character varying(8),
  vulling integer NOT NULL,
  spoor integer NOT NULL,
  put integer NOT NULL,
  CONSTRAINT vulling_pkey PRIMARY KEY (vulling, put, spoor)
)

I read this threads on stackoverflow but i don't fully understand what they are saying:

PostgreSQL constraint problems

Postgresql constraint

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

I hope somebody can help me out. Im very new to postgres and dont fully understand how the foreign keys work.

Cheers

Gabriel's Messanger · Accepted Answer · 2015-11-23T12:36:46.623

0

On (referenced) table vulling you need to have uniqe values for your foreign key. But you don't. You have unique values for triplet (vulling, put, spoor) which not nessecerly mean uniqness of pair (spoor, vulling).

Consider

vulling
======================
vulling | put | spoor
----------------------
1       | 0   | 0
1       | 1   | 0

That two rows are uniqe in terms of triplet but not as a pair of vulling and spoor. In the end you should either add UNIQUE(vulling, spoor) to your table definition, or manage many-to-many relation using helper-table.

Alternatively you may reverse te foreign key def:

ALTER TABLE vulling
ADD FOREIGN KEY (spoor,vulling)
REFERENCES put_25_vlak_1_spoor(spoor,vulling);

edited Nov 23 '15 at 12:36

answered Nov 23 '15 at 12:10

Gabriel's Messanger

3,213
17
31

i also though that and i search with this sql: select spoor, vulling from vulling group by spoor,vulling having count (*) > 1 and select spoor, vulling from put_25_vlak_1_spoor group by spoor,vulling having count (*) > 1 but there were no double records. I have some double if i do only spoor. Maybe thats the problem? – waywer Nov 23 '15 at 12:13
It's not only about no-dublication it is about not **allowing** dublication to occure ever. You get that by defining unique constraint for pair of calumns. – Gabriel's Messanger Nov 23 '15 at 12:16
i made both of the columns a primary keys. In that way there is no dublication(at least i thought so). If i make it unique the column, i cant do something like this: spoor =10 vulling =1 and spoor =10 and vulling = 2. But i want to do that in my database – waywer Nov 23 '15 at 12:26
Read my answer (i've edited it a little bit) in table `vulling` you create triple primary key `(vulling, put, spoor)` that doesn't mean that `(spoor, vulling)` in that table is unique. But it must be in your case to define such a `foreign key` as you want. Or you may try to reverse the foreign key. – Gabriel's Messanger Nov 23 '15 at 12:34
it works! thanks, but still have a problem. i removed the primary ket on put. But i want vulling also be connected to another table, i need the primary key also on put. I get a new error: ERROR: connot drop constraint vulling_pkey on tabel vulling because objects depend on it. Detail: constraint pu_25_vlak_1_spoor_spoor_fkey on table put _25_vlak_1_spoor depends on index vulling_pkey. HINT Use DROP...CASCADE to drop the depend object too – waywer Nov 23 '15 at 12:51
Now you have the same situation but in "the other side". Before you was not able to create foreign key because of wrong `PK`. Now you cannot delete `Primary Key` because it takes part in `FOREIGN KEY` (FK). First drop `foreign key` correct PK, and then recraete FK. – Gabriel's Messanger Nov 23 '15 at 13:07
thanks you very much, i understand now how it works! – waywer Nov 23 '15 at 13:14

score 0 · Answer 2 · answered Nov 23 '15 at 12:13

0

Did you have data in the vulling table? You can't add data to the put_25_vlak_1_spoor if in the vulling doesn't exist the same data in rows spoor,vulling.

i.e. if you want to add to the put_25_vlak_1_spoor a record, where spoor=111 and vulling=222, in the vulling have to be a row where spoor=111 and vulling=222

answered Nov 23 '15 at 12:13

B. Kostya

36
2

i checked it and there were indeed two records that existed in put25_vlak_1_spoor that didn;t exist in vulling. I added them to the table vulling. But i still have the same error. ps. I got more records in vulling then put25_vlak_1_spoo – waywer Nov 23 '15 at 12:21

PostgreSQL constraint error

2 Answers2