Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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-----


pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group