pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable inheritance problem
2+ messages / 2 participants
[nested] [flat]
* Table inheritance problem
@ 2009-07-18 17:09 Gianvito Pio <pio.gianvito@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Gianvito Pio @ 2009-07-18 17:09 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have 3 tables: persons, operators and persons_position.
This is a semplified examples of their structures:
CREATE TABLE persons
(id varchar NOT NULL,
CONSTRAINT "PK_Persons" PRIMARY KEY(id));
CREATE TABLE operators
(id varchar NOT NULL,
CONSTRAINT "PK_Operators" PRIMARY KEY(id))
INHERITS(persons);
CREATE TABLE persons_position
(id bigserial NOT NULL,
person varchar NOT NULL);
and then there is a FOREIGN KEY CONSTRAINT from persons_position.person TO persons.id.
If I insert a tuple in operators...it results also in persons, but when I insert a tuple in persons_position, it says me I have violated the foreing key constraints. So it appears that the tuple really ISN'T in the persons table and the foreing key check fails.
How could I solve it, keeping the inheritance there?
Thanks
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Table inheritance problem
@ 2009-07-20 13:32 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
parent: Gianvito Pio <pio.gianvito@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Andreas Wenk @ 2009-07-20 13:32 UTC (permalink / raw)
To: Gianvito Pio <pio.gianvito@gmail.com>; pgsql-sql
Gianvito Pio schrieb:
> Hello,
> I have 3 tables: persons, operators and persons_position.
>
> This is a semplified examples of their structures:
>
> CREATE TABLE persons
> (id varchar NOT NULL,
> CONSTRAINT "PK_Persons" PRIMARY KEY(id));
>
>
> CREATE TABLE operators
> (id varchar NOT NULL,
> CONSTRAINT "PK_Operators" PRIMARY KEY(id))
> INHERITS(persons);
>
> CREATE TABLE persons_position
> (id bigserial NOT NULL,
> person varchar NOT NULL);
>
> and then there is a FOREIGN KEY CONSTRAINT from persons_position.person
> TO persons.id.
>
> If I insert a tuple in operators...it results also in persons, but when
> I insert a tuple in persons_position, it says me I have violated the
> foreing key constraints. So it appears that the tuple really ISN'T in
> the persons table and the foreing key check fails.
>
> How could I solve it, keeping the inheritance there?
> Thanks
Hi,
sure it does throw that error. Your foreign key constraint is wrong. The check will look
like this:
test=# insert into persons_position values(2,'andy');
ERROR: insert or update on table "persons_position" violates foreign key constraint
"persons_position_persons_fkey"
DETAIL: Key (person)=(andy) is not present in table "persons".
You could solve it by altering the table persons:
test=# alter table persons add column person varchar(255);
ALTER TABLE
test=# insert into persons_position values(2,'andy');
INSERT 0 1
Or change the foreign key constraint in persons_position to id (be careful - the datatypes
are different)
Cheers
Andy
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-07-20 13:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-07-18 17:09 Table inheritance problem Gianvito Pio <pio.gianvito@gmail.com>
2009-07-20 13:32 ` Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox