On Thu, 20 Jun 2002, Dave Page wrote:
> I have the following tables in a 7.2 database:
>
> -- Table: sys_users
> CREATE TABLE sys_users (
> sys_guid int4 DEFAULT nextval('"sys_users_sys_guid_seq"'::text) NOT
> NULL,
> sys_email varchar(64),
> sys_name varchar(64) NOT NULL,
> sys_superuser bool DEFAULT 'f'::bool,
> sys_active bool DEFAULT 't'::bool,
> sys_tokens text,
> sys_password varchar(128),
> sys_comments text,
> CONSTRAINT sys_users_pkey PRIMARY KEY (sys_guid)
> ) WITH OIDS;
>
> -- Table: dms_categories
> CREATE TABLE dms_categories (
> dms_guid int4 DEFAULT nextval('"dms_categories_dms_guid_seq"'::text)
> NOT NULL,
> dms_created timestamptz,
> dms_name varchar(64) NOT NULL,
> dms_description text,
> dms_parent_category int4,
> dms_owner varchar(64) NOT NULL,
> dms_deleted bool DEFAULT 'f'::bool,
> CONSTRAINT dms_categories_pkey PRIMARY KEY (dms_guid)
> ) WITH OIDS;
>
> -- Table: dms_acl
> CREATE TABLE dms_acl (
> dms_guid int4 DEFAULT nextval('"dms_acl_dms_guid_seq"'::text) NOT
> NULL,
> dms_category int4 NOT NULL,
> dms_user int4 NOT NULL,
> dms_read bool DEFAULT 't'::bool,
> dms_write bool DEFAULT 'f'::bool,
> CONSTRAINT dms_acl_pkey PRIMARY KEY (dms_guid),
> CONSTRAINT dms_acl_dms_categories FOREIGN KEY (dms_category)
> REFERENCES dms_categories (dms_guid) ON DELETE CASCADE ON UPDATE CASCADE
> NOT DEFERRABLE INITIALLY IMMEDIATE,
> CONSTRAINT dms_acl_sys_users FOREIGN KEY (dms_user) REFERENCES
> sys_users (sys_guid) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE
> INITIALLY IMMEDIATE
> ) WITH OIDS;
>
> If I try to insert a record in dms_acl:
>
> INSERT INTO dms_acl (dms_category, dms_user, dms_read, dms_write) VALUES
> (102, 51, 'Y', 'Y')
>
> I get the error:
>
> ERROR: dms_acl_sys_users referential integrity violation - key
> referenced from dms_acl not found in sys_users
>
> A select on sys_users confirms that I do have a record with sys_guid =
> 51. I have also tried this with other known values from
> sys_users.sys_guid & always get the error.
>
> Any ideas gratefully received!
Wierd. Is there any inheritance or other such in the schema (in which case
you need to use ONLY on the selects to see if the rows exist)? I'm not
sure what else would make it miss the data, but if you can send a dump
or example that illustrates, I can take a look at what it's doing.
