2

I'm trying to add a foreign key of "prefectures" table in "centresdevote" table. But I'm having this error while trying to add a foreign key to "centresdevote" table. I had no problem adding foreign keys to other tables in my database by the way, and I can't seem to figure out why this error pops up only for this one. I'm working on Postgresql 9.2 and I've added the foreign keys for the other tables manually. I tried also using SQL code as follows :

ALTER TABLE centresdevote 
ADD CONSTRAINT objectid 
FOREIGN KEY (objectid) REFERENCES prefectures (objectid);

Here's the error

ERROR:  insert or update on table "centresdevote " violates foreign key constraint "objectid"
DETAIL:  Key (objectid)=(10) is not present in table "prefectures".

Here's the structure of the two tables :

"prefectures" table :

objectid integer NOT NULL,
nomprefecture character varying(70),
shape st_geometry,
respcommissionrecensement character varying(70),
CONSTRAINT prefectures_pkey PRIMARY KEY (objectid)

"centresdevote" table :

objectid integer NOT NULL,
arabicname character varying(70),
frenchname character varying(70),
shape st_point,
CONSTRAINT centresdevote_pkey PRIMARY KEY (objectid)

Thanks in advance!

1 Answer 1

4

In the table centresdevote you have a row where the value of objectid is 10. As you are trying to create a FOREIGN KEY CONSTRAINT there is a check that there is a matching value in the column objectid in the table prefectures for all the rows in centresdevote. In prefectures there is no row with objectid = 10.

I suggest you read The PostgreSQL Documentation - 5.3.5. Foreign Keys and the W3Schools page about foreign keys. The whole point of the foreign keys is that they limit what you can insert into the field. From PostgreSQL Documentation:

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.

Adding foreign key is not mandatory and you can do every kind of query without it.

12
  • Thank you @simo Kivistö for your quick reply. Indeed I only have 9 rows in "prefectures" table, while I have 46 rows in "centresdevote" table. But that's the data I have, what am I supposed to do then ?
    – GeoSal
    Mar 20, 2015 at 11:22
  • While adding foreign keys for the other tables I have, and although the number of rows weren't the same for the concerned tables I didn't have this error. How come?
    – GeoSal
    Mar 20, 2015 at 11:23
  • Error in your data? Is there a missing préfecture?
    – Vérace
    Mar 20, 2015 at 11:28
  • Thanks for your reply @Vérace. Well I have 9 rows in préfecture table, and 46 rows in "centresdevote" table. Does the number of rows in the two tables need to match for the foreign key to be added?! I don't think it should be, right? Otherwise why I didn't have any problem adding a foreign key to other tables knowing that the number of rows aren't matched !
    – GeoSal
    Mar 20, 2015 at 11:34
  • Added some explanation. The number of rows don't need to match but the values in the columns must match. Mar 20, 2015 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.