[GENERAL] foreign keys and inheritance problem
hi, I am in some trouble with my tables defined using inheritance, This is a semplified test case: --- create table sub1( name1 text) inherits(father); create table sub2( name2 text) inherits(father); create table other (description text, id integer); -- I know, the contraints is not checked in sub1 and sub2 ALTER TABLE father ADD UNIQUE(id); ALTER TABLE other ADD FOREIGN KEY (id) REFERENCES father(id); insert into sub1 (id,name1) VALUES(1,'row1 in sub1'); insert into sub2 (id,name2) VALUES(2,'row1 in sub2'); select * from father; id 1 2 I can't insert data in other table: - test=# insert into other(id,description) VALUES(1,'test'); ERROR: insert or update on table other violates foreign key constraint other_id_fkey DETAIL: Key (id)=(1) is not present in table father. - Is there a way to do this thing? Or I must remove the foreign key constraint? thank you Edoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foreign keys and inheritance problem
On 12/08/10 18.59, Edoardo Panfili wrote: hi, I am in some trouble with my tables defined using inheritance, This is a semplified test case: --- create table sub1( name1 text) inherits(father); create table sub2( name2 text) inherits(father); create table other (description text, id integer); -- I know, the contraints is not checked in sub1 and sub2 ALTER TABLE father ADD UNIQUE(id); ALTER TABLE other ADD FOREIGN KEY (id) REFERENCES father(id); insert into sub1 (id,name1) VALUES(1,'row1 in sub1'); insert into sub2 (id,name2) VALUES(2,'row1 in sub2'); select * from father; id 1 2 I can't insert data in other table: - test=# insert into other(id,description) VALUES(1,'test'); ERROR: insert or update on table other violates foreign key constraint other_id_fkey DETAIL: Key (id)=(1) is not present in table father. - Is there a way to do this thing? Or I must remove the foreign key constraint? trigger solution, it seems ok but I am still searching for a declarative one. CREATE OR REPLACE FUNCTION insert_veto() RETURNS trigger AS $BODY$ DECLARE present boolean; BEGIN present := exists (select * from father where id=NEW.id) ; IF present THEN return NULL; ELSE RETURN NEW; END IF; END $BODY$ LANGUAGE 'plpgsql' CREATE TRIGGER veto BEFORE INSERT OR UPDATE ON other FOR EACH ROW EXECUTE PROCEDURE insert_veto(); Edoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foreign keys and inheritance problem
Edoardo Panfili edoa...@aspix.it writes: On 12/08/10 18.59, Edoardo Panfili wrote: I am in some trouble with my tables defined using inheritance, No, foreign keys do not play very nicely with inheritance. There is some explanation in the manual, in the Caveats subsection under Inheritance --- see bottom of this page: http://www.postgresql.org/docs/8.4/static/ddl-inherit.html regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foreign keys and inheritance problem
On 12/08/10 20.44, Tom Lane wrote: Edoardo Panfiliedoa...@aspix.it writes: On 12/08/10 18.59, Edoardo Panfili wrote: I am in some trouble with my tables defined using inheritance, No, foreign keys do not play very nicely with inheritance. There is some explanation in the manual, in the Caveats subsection under Inheritance --- see bottom of this page: http://www.postgresql.org/docs/8.4/static/ddl-inherit.html thank you, I must read with more attenction the page. I stop the search for a declarative solution, triggers or no check. thank you again Edoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign keys and inheritance
I have two classes of objects, A and B, where B is just a special case of A. (I.e., to describe a B-type object I need to specify the same fields as for an A-type object, plus a whole bunch additional fields specific to B alone.) Furthermore, there's a third class T that is in a many-to-one relation with A (and hence also B) objects. The question is, what's the best practice for implementing this situation in PostgreSQL. My first idea was to define B as inheriting from A, which is OK, except that I have not figured out how to implement the reference from T. Is inheritance indeed the right tool for this problem, or should I use a different approach? TIA! kj ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Foreign keys and inheritance
On Mon, 2007-11-19 at 14:36 -0500, Kynn Jones wrote: I have two classes of objects, A and B, where B is just a special case of A. (I.e., to describe a B-type object I need to specify the same fields as for an A-type object, plus a whole bunch additional fields specific to B alone.) Furthermore, there's a third class T that is in a many-to-one relation with A (and hence also B) objects. The question is, what's the best practice for implementing this situation in PostgreSQL. My first idea was to define B as inheriting from A, which is OK, except that I have not figured out how to implement the reference from T. Is inheritance indeed the right tool for this problem, or should I use a different approach? I would probably do something like: CREATE TABLE A (a_id INT PRIMARY KEY, a_attr text); CREATE TABLE B (b_id INT PRIMARY KEY, a_id int references A(a_id) UNIQUE, b_attr text); CREATE TABLE T (t_id INT PRIMARY KEY, a_id int references A(a_id), t_attr text); I can't tell whether you mean that every A has many T or vice versa, but minor modification will make it work in the opposite direction. To look at all A objects, you just look in table A. You can do A NATURAL JOIN T to realize the many-to-one relationship from A to T. You can do A NATURAL JOIN B to see all B objects (which have a_attr since they are a special case of A). This is a normal relational design that is very flexible and doesn't require the PostgreSQL-specific INHERITANCE feature. You don't need to use natrual joins of course, it was just easier for this example. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys and inheritance
Kynn Jones wrote: I have two classes of objects, A and B, where B is just a special case of A. (I.e., to describe a B-type object I need to specify the same fields as for an A-type object, plus a whole bunch additional fields specific to B alone.) Furthermore, there's a third class T that is in a many-to-one relation with A (and hence also B) objects. The question is, what's the best practice for implementing this situation in PostgreSQL. My first idea was to define B as inheriting from A, which is OK, except that I have not figured out how to implement the reference from T. Is inheritance indeed the right tool for this problem, or should I use a different approach? It seems that inheritance is precisely what you want. WRT yout table T you should be able to join to B in the same way you would join to A. But perhaps you should give an example of both B T (and maybe A). brian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Foreign keys and inheritance
Kynn Jones escribió: I have two classes of objects, A and B, where B is just a special case of A. (I.e., to describe a B-type object I need to specify the same fields as for an A-type object, plus a whole bunch additional fields specific to B alone.) Furthermore, there's a third class T that is in a many-to-one relation with A (and hence also B) objects. The question is, what's the best practice for implementing this situation in PostgreSQL. My first idea was to define B as inheriting from A, which is OK, except that I have not figured out how to implement the reference from T. Is inheritance indeed the right tool for this problem, or should I use a different approach? It would be the right tool if the FKs worked :-( Sadly, they don't. alvherre=# create table foo (a int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo cCREATE TABLE alvherre=# create table bar (a int not null references foo); CREATE TABLE alvherre=# create table baz () inherits (foo); CREATE TABLE alvherre=# insert into baz values (1); INSERT 0 1 alvherre=# select * from foo; a --- 1 (1 row) alvherre=# insert into bar values (1); ERROR: insert or update on table bar violates foreign key constraint bar_a_fkey DETAIL: Key (a)=(1) is not present in table foo. This is a Postgres shortcoming, but I don't think there's anybody working on fixing it, so don't hold your breath. Uniqueness also fails in inheritance: for example alvherre=# insert into foo values (1); INSERT 0 1 alvherre=# select * from foo; a --- 1 1 (2 rows) (Note that column is the PK) -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ Investigación es lo que hago cuando no sé lo que estoy haciendo (Wernher von Braun) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys and inheritance
On Mon, 2007-11-19 at 17:19 -0300, Alvaro Herrera wrote: Kynn Jones escribió: I have two classes of objects, A and B, where B is just a special case of A. (I.e., to describe a B-type object I need to specify the same fields as for an A-type object, plus a whole bunch additional fields specific to B alone.) Furthermore, there's a third class T that is in a many-to-one relation with A (and hence also B) objects. The question is, what's the best practice for implementing this situation in PostgreSQL. My first idea was to define B as inheriting from A, which is OK, except that I have not figured out how to implement the reference from T. Is inheritance indeed the right tool for this problem, or should I use a different approach? It would be the right tool if the FKs worked :-( Sadly, they don't. I don't think it's that bad of a situation. It would be great if PostgreSQL did support keys across tables, but it's not necessary for a good design in his case. The difference between using inheritance and just using multiple tables (like the alternative that I suggested) is the difference between vertically partitioning and horizontally partitioning. Both seem like good choices to me. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Foreign keys, table inheritance, and TRUNCATE
Here's something I've just noticed: CREATE TABLE foo (f INTEGER PRIMARY KEY); INSERT INTO foo VALUES (1); CREATE TABLE bar (b INTEGER REFERENCES foo); CREATE TABLE bar1 () INHERITS (bar); INSERT INTO bar1 VALUES (1); This is quite correct: TRUNCATE foo; ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table bar references foo. HINT: Truncate table bar at the same time, or use TRUNCATE ... CASCADE. But: TRUNCATE foo, bar; SELECT * FROM bar; b --- 1 (1 row) SELECT * FROM foo; f --- (0 rows) Whoops. The referential constraint has been violated. Perhaps it's a good idea to extend TRUNCATE on a parent table to all children? -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Foreign keys, table inheritance, and TRUNCATE
Florian Weimer wrote: Here's something I've just noticed: CREATE TABLE foo (f INTEGER PRIMARY KEY); INSERT INTO foo VALUES (1); CREATE TABLE bar (b INTEGER REFERENCES foo); CREATE TABLE bar1 () INHERITS (bar); INSERT INTO bar1 VALUES (1); This is quite correct: No, it isn't; try leaving the first INSERT out: alvherre=# CREATE TABLE foo (f INTEGER PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE alvherre=# CREATE TABLE bar (b INTEGER REFERENCES foo); CREATE TABLE alvherre=# CREATE TABLE bar1 () INHERITS (bar); CREATE TABLE alvherre=# INSERT INTO bar1 VALUES (1); INSERT 0 1 alvherre=# select * from bar; b --- 1 (1 fila) alvherre=# select * from foo; f --- (0 filas) There is a bug here, but it's not in TRUNCATE. FKs don't work with inheritance. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
