Referencing keys in tables that are inherited.

From: J A Stride <j(dot)a(dot)stride(at)ncl(dot)ac(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Referencing keys in tables that are inherited.
Date: 2003-03-28 19:41:51
Message-ID: 200303281942.01367.j.a.stride@ncl.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

-----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 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+hKWHIxynSpxMjtERAu8CAKCx341wijTz08JXgMoX8Fqr6dajJACdG13I
FjGn/RSbiYqpIKX2TzLdVLc=
=6GN6
-----END PGP SIGNATURE-----

Browse pgsql-novice by date

  From Date Subject
Next Message LaBranche, Kevin 2003-03-28 19:48:21 Insert fails from VB only when table is empty...?
Previous Message Ron Johnson 2003-03-28 16:40:05 Re: Getting data out of MS Access with only Linux?