Thread: FOREIGN KEY CONSTRAINT AND INHERITANCE
Hi, All!
I have the tables as defined:
create table style
(
ID_STYLE INT4 not null,
STYLE_STRING VARCHAR not null,
constraint PK_STYLE primary key (ID_STYLE)
);
create table style_type_object
(
ID_TYPE_OBJECT INT4 not null,
ID_STYLE INT4 not null,
ZOOMABLE BOOL not null default false,
START_SCALE INT4 not null,
END_SCALE INT4 not null
);
alter table STYLE_TYPE_OBJECT
add constraint FK_ID_STYLE foreign key (ID_STYLE)
references STYLE (ID_STYLE)
on delete restrict on update restrict;
create table style_1
(
constraint PK_STYLE_1 primary key (id_style),
check (id_style between 1 and 10000)
) inherits (style);
create rule r_style_1 as on insert to style
where (id_style between 1 and 10000)
do instead
insert into style_1 (id_style, style_string)
values(new.id_style, new.style_string);
insert into style values (2, 'PEN(w:1px)' );
vka7=# select id_style, style_string from style;
id_style | style_string
----------+--------------
2 | PEN(w:1px)
vka7=# insert into style_type_object values(1, 2, false, 0, 0);
ERROR: insert or update on table "style_type_object" violates foreign key constraint "fk_id_style"
DETAIL: Key (id_style)=(2) is not present in table "style".
THE QUESTION IS: are there any abilities to check foreign key constraint not only in master table, but also in all tables that inherited the master?
Thanks in advance.
Sergey Karin
I have the tables as defined:
create table style
(
ID_STYLE INT4 not null,
STYLE_STRING VARCHAR not null,
constraint PK_STYLE primary key (ID_STYLE)
);
create table style_type_object
(
ID_TYPE_OBJECT INT4 not null,
ID_STYLE INT4 not null,
ZOOMABLE BOOL not null default false,
START_SCALE INT4 not null,
END_SCALE INT4 not null
);
alter table STYLE_TYPE_OBJECT
add constraint FK_ID_STYLE foreign key (ID_STYLE)
references STYLE (ID_STYLE)
on delete restrict on update restrict;
create table style_1
(
constraint PK_STYLE_1 primary key (id_style),
check (id_style between 1 and 10000)
) inherits (style);
create rule r_style_1 as on insert to style
where (id_style between 1 and 10000)
do instead
insert into style_1 (id_style, style_string)
values(new.id_style, new.style_string);
insert into style values (2, 'PEN(w:1px)' );
vka7=# select id_style, style_string from style;
id_style | style_string
----------+--------------
2 | PEN(w:1px)
vka7=# insert into style_type_object values(1, 2, false, 0, 0);
ERROR: insert or update on table "style_type_object" violates foreign key constraint "fk_id_style"
DETAIL: Key (id_style)=(2) is not present in table "style".
THE QUESTION IS: are there any abilities to check foreign key constraint not only in master table, but also in all tables that inherited the master?
Thanks in advance.
Sergey Karin
am Wed, dem 25.04.2007, um 19:25:46 +0400 mailte Sergey Karin folgendes: > vka7=# insert into style_type_object values(1, 2, false, 0, 0); > ERROR: insert or update on table "style_type_object" violates foreign key > constraint "fk_id_style" > DETAIL: Key (id_style)=(2) is not present in table "style". Read my answer yesterday in the thread "inherits and foreign key problem", this mailinglist. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net