I am facing the classic pgsql ORDBMS problem:
create sequence entity_id_seq;
create table entity (
entity_id int not null default nextval('entity_id_seq'),
primary key (entity_id)
);
create table person (
first_name varchar(32) not null,
last_name varchar(32) not null,
primary key (entity_id)
) inherits (entity);
create sequence entity_phone_id_seq;
create table entity_phone (
entity_phone_id int not null default nextval('entity_phone_id_seq'),
entity_id int not null,
phone varchar(32),
constraint entity_phones_entity_id_fk foreign key (entity_id)
references entity on delete cascade,
primary key (entity_phone_id)
);
-- Insert the person
insert into person (first_name,last_name) values ('Alex','Turner');
-- Attempt to insert phone
insert into entity_phone select max(entity_id), '610 495 5000' from person;
Insert fails with a foreign key constraint error because entity_phone
points to entity, not person, and the rows aren't physicaly in entity,
they are in person.
Two questions:
1) Why can't we make this work the 'right' way - not doing so either
breaks OO or brakes RDBMS. 1)a) Whats the point of an RDBMS if you
can't specify foreign keys that work because you choose to use OO
features (I somewhat appreciate that there is a trigger inheritance
problem, can't we just define the rules and order of precident and
solve it)?
2) Whats the best way to manage this with triggers. Obviously one can
create a trigger on entity and on person for delete so that it removes
corresponding rows in entity_phone. But whats the best way to create
a trigger that ensures that entity_ids that are used in entity_phone
exist in entity and it's subtables thats fast. You could do:
select into foo where entity_id=NEW.entity_id from entity;
if not found then
raise exception 'Value for entity_id not found in entity';
endif;
-- is this the fastest way?
currently using 7.4.5 on RHEL3, but will be upgrading to 8.0 this week.
Alex Turner
NetEconomist
