Re: Referencing Problem/bug?

On Fri, 28 Mar 2003, J A Stride wrote:

> I have a problem with referencing keys. I have several tables:
>
> Resource table with a unique serial:
>
> create table resource (id bigserial not null unique, type int, units int not
> null, stdrate int not null default 0, projectid bigint references project(id)
> on update cascade on delete cascade not null, notes varchar, primary key
> (id));
>
> A person resource that inherits from the resource:
>
> create table personresource (personid bigint references person(id) on update
> cascade on delete cascade not null,primary key (projectid,personid)) inherits
> (resource);
>
> A resource group:
>
> create table resourcegroup (id bigserial not null unique, projectid bigint
> references project(id) on update cascade on delete cascade not null, personid
> bigint references person(id) on update cascade on delete cascade not null,
> name varchar not null, primary key(projectid, name));
>
> A cross reference table that contains all the resources in the resource group:
>
> create table resourcegroupxref (resourcegroupid bigint references
> resourcegroup(id) on update cascade on delete cascade not null, resourceid
> bigint references resource(id) on update cascade on delete cascade not null,
> primary key(resourcegroupid,resourceid));
>
> However if I do an insert into the person resource (which means that there is
> a value in the resource table) and the resource group I cannot put the id's
> from the insert's into the cross reference table as it fails with:
>
> ERROR: <unnamed> referential integrity violation - key referenced from
> resourcegroupxref not found in resource
>
> Is there a reason that I am missing as to why I cannot reference a value in a
> table that is inherited from?

Foreign key constraints currently go only to the explicitly named table
(and not any sub-tables). Inheritance needs a bunch of work in general
(for example, AFAIK you can insert duplicate id values into person in the
above table schema if you specify the column, and even if you put a unique
constraint on it explicitly, you could duplicate values in resource).

There've been some workaround suggestions discussed in the past that
should be in the archives (although none were pretty as i remember)