Referencing Problem/bug?

From: J A Stride <j(dot)a(dot)stride(at)ncl(dot)ac(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Referencing Problem/bug?
Date: 2003-03-28 22:46:42
Message-ID: 200303282246.51341.j.a.stride@ncl.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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?

Thanks

Jake
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE+hNDZIxynSpxMjtERAnhKAKCx5xwbZ9wnBBVLz4Sqmn+C6ribPQCgvMDD
Sgn0u97/rlK1DMxSx5JqCY0=
=AfQ7
-----END PGP SIGNATURE-----

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Smith 2003-03-28 22:48:00 Re: Please help with this error message
Previous Message Flower Sun 2003-03-28 22:25:58 Re: About OIDs