On Fri, 22 Jun 2007 18:23:44 -0300 "Daniel van Ham Colchete" <daniel.colchete@xxxxxxxxx> wrote: Hi, As far as I read the documents(see below), it seems to be correct that no error message occurred in your case. http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html -- All check constraints and not-null constraints on a parent table are -- automatically inherited by its children. Other types of constraints -- (unique, primary key, and foreign key constraints) are not inherited. For example, you probably need to declare CREATE TABLE like the below. ?@CREATE TABLE regsemail (dID serial PRIMARY KEY, rID integer, email text, FOREIGN KEY(rID) REFERENCES regs(rID) ON DELETE CASCADE); CREATE TABLE regsemail_00 (CHECK ( rID >= 0 AND rID < 10 ), FOREIGN KEY(rID) REFERENCES regs_00(rID) ON DELETE CASCADE) INHERITS(regsemail); By the way, why will you have such a huge number of e-mail addresses ? -- Masaru Sugawara > People, > > either I don't understand how p > People, > > either I don't understand how partitions works or I think I found a bug > here. > > I'm using PostgreSQL-8.2.4 with Gentoo. > > The code explains: > > # First I create the table regs with 2 partitions: > create table regs (rID serial primary key, name text, number int); > create table regs_00 ( CHECK ( number >= 00 AND number < 10 )) INHERITS > (regs); > create rule insert_regs_00 AS ON INSERT TO regs WHERE ( number >= 00 AND > number < 10 ) DO INSTEAD INSERT INTO regs_00 VALUES ( NEW.rID, NEW.name, > NEW.number ); > create table regs_10 ( CHECK ( number >= 10 AND number < 20 )) INHERITS > (regs); > create rule insert_regs_10 AS ON INSERT TO regs WHERE ( number >= 10 AND > number < 20 ) DO INSTEAD INSERT INTO regs_10 VALUES ( NEW.rID, NEW.name, > NEW.number ); > > # Them I create the table regsemail also with 2 partitions but with a > foreign key: > create table regsemail (dID serial primary key, fk_regs_id integer > REFERENCES regs (rID) ON DELETE CASCADE, email text); > create table regsemail_00 ( CHECK ( fk_regs_id >= 0 AND fk_regs_id < 10 )) > INHERITS (regsemail); > CREATE RULE insert_regsemail_00 AS ON INSERT TO regsemail WHERE ( fk_regs_id > >= 0 AND fk_regs_id < 10 ) DO INSTEAD INSERT INTO regsemail_00 VALUES ( > NEW.dID, NEW.fk_regs_id, NEW.email ); > create table regsemail_10 ( CHECK ( fk_regs_id >= 10 AND fk_regs_id < 20 )) > INHERITS (regsemail); > CREATE RULE insert_regsemail_10 AS ON INSERT TO regsemail WHERE ( fk_regs_id > >= 10 AND fk_regs_id < 20 ) DO INSTEAD INSERT INTO regsemail_10 VALUES ( > NEW.dID, NEW.fk_regs_id, NEW.email ); > > # Insert four rows in regs (rID will go from 1 to 4): > insert into regs (name, number) values ('Daniel', 4); > insert into regs (name, number) values ('Daniel', 14); > insert into regs (name, number) values ('Daniel', 5); > insert into regs (name, number) values ('Daniel', 15); > > # Insert a 'invalid' row in regsemail > insert into regsemail (fk_regs_id, email) values (6, 'daniel@xxxxxxxxxxx'); > # END! > > I should get an error saying something like "...violates foreign key > constraint..." but I'm not getting anything. That's the bug. If I don't have > the partitions them I get the error message (as I think I should). > > The problem I'm trying to solve is: I'll have a 1.8 billion rows table > (regs) and another one having at least one row to each row from the first > one. The solution is very simple: partitions. The 1.8 billion rows is > distributed uniformly in the days of the year, so I'll create one partition > for each day. But I have to do something similar with the second table as > well otherwise I wouldn't win much if I had to do a JOIN. I was testing how > foreign keys would work in this case and ran into this. > > Is this really a bug? If not, what am I doing wrong please? > > Best regards, > Daniel
