Thread: Referencing Problem/bug?
-----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-----
If you format the table deffintions like this:
create table name(
col1_name col1_type col1_qualifiers,
col2_name col2_type col2_qualifiers
);
-----OR EVEN BETTER------
create table name(
col1_name col1_type col1_qualifiers,
col2_name col2_type col2_qualifiers
);
You will get more takers for helping you.
J A Stride wrote:
> -----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-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
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)
On Fri, 2003-03-28 at 22:46, J A Stride wrote: > 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 I had precisely this problem. I am working around it by using a couple of triggers written in plpython that look in a referential_constraint table that has rows like: id | primary_key | foreign_key ----+-------------+---------------------------- 1 | domain.id | domain_orderitem.domain_id Now, atm I'm leaving as much as I can as "real" postgres references. I believe that it will be faster this way. The trigger executes before updates, deletes and inserts and just throws an exception if anything goes wrong. I'll probably release it but not just yet - it's a bit of a mess since it was the first thing I used plpython for (it's also largely untested) It needs some other things, like preventing inserts on base tables, and some triggers on the referential constraint table to go along and add the triggers to the appropriate tables. It can be done, but it's the hackiest thing EVER. HINT: you won't get it to work in plpgsql if you are inclined to try. HTH, James.