Today I started to port a MySql database to PostgreSQL and I have a problem with the foreign keys and their 'sharing'
I don't know how to explain that so here is some pseudo code of the create script:
create table id_generator (
id serial PRIMARY KEY,
description varchar(50) );
create table parent (
id REFERENCES id_generator(id),
--content );
create table child (
id REFERENCES id_generator(id),
id_parent REFERENCES parent(id),
--content );
So I use one table as 'id_generator' to create the id's and to give the other tables a reference to a value table. In MySql I had no problems with that so I want that to work in PostgreSQL as well.
When running that script I get the error, that I need a primary key / unique on the table parent to create a reference on it. so I changed it into:
create table id_generator (
id serial PRIMARY KEY,
description varchar(50) );
create table parent (
id REFERENCES id_generator(id) PRIMARY KEY,
--content );
create table child (
id REFERENCES id_generator(id),
id_parent REFERENCES parent(id),
--content );
so the server accepts the create script and all is set up for some inserts.
I create the first id with:
insert into id_generator(description) values("parentID");
then I want to add a parent:
insert into parent(id, /*content*/) values(1, /*content*/);
that also works as expected, so I need to insert a child for parent:
insert into id_generator(description) values("childID");
insert into child(id,id_parent)values(2,1);
and here I get the error message "ERROR: duplicate key value violates unique constraint DETAIL: key (id_parent)=(1) allready exists"
edit:
\d child;
Tabelle ╗public.child½
Spalte | Typ | Attribute
-------------------+---------+-----------
id | integer | not null
id_parent | integer |
Indexe:
"child_pkey" PRIMARY KEY, btree (id)Fremdschlⁿssel-Constraints:
"child_id_parent_fkey" FOREIGN KEY (id_parent) REFERENCES parent(id)
"child_id_generator_fkey" FOREIGN KEY (id) REFERENCES id_generator(id)
how can I solve that?
=> \d childand aselect * from childchildis empty