Thread: foreign Key problem

Hi,

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!

Regards, Dave.

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.

Following up my own message...

> -----Original Message-----
> From: Dave Page
> Sent: 21 June 2002 08:29
> To: 'Stephan Szabo'
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] foreign Key problem
>
>
> I'll see if I can get a test example to work (==fail) with
> non-sensitive data as soon as England have beaten Brazil... :-)
>

Oh well, you can't win 'em all :-(

Anyway, here's a simple test case illustrating this problem.

CREATE TABLE t1 (t1_id int4 PRIMARY KEY);
CREATE TABLE t2 (t2_id int4 PRIMARY KEY) INHERITS(t1);
CREATE TABLE t3 (t3_id int4 PRIMARY KEY);
CREATE TABLE t4 (
  t4_id1 int4,
  t4_id2 int4,
  CONSTRAINT t4_t1 FOREIGN KEY (t4_id1) REFERENCES t1 (t1_id) ON DELETE
CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT t4_t3 FOREIGN KEY (t4_id2) REFERENCES t3 (t3_id) ON DELETE
CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO t2 (t1_id, t2_id) VALUES (1, 2);
INSERT INTO t3 (t3_id) VALUES (3);
INSERT INTO t4 (t4_id1, t4_id2) VALUES (1, 3);

The error also occurs in 2 day old 7.3 dev code.

Regards, Dave.

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: 20 June 2002 23:14
> To: Dave Page
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] foreign Key problem
>
>
> On Thu, 20 Jun 2002, Dave Page wrote:
>
> > 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.
>

The only inheritance in the DB is another table that inherits sys_users.
Actually, come to think of it most of the records in sys_users would
have come from there. I can't send a full dump as it's data that comes
under the UK Data Protection Act, but the table that inherits sys_users
looks like:

-- Table: hr_staff
CREATE TABLE hr_staff (
  hr_payroll_no varchar(8),
  hr_title varchar(128),
  hr_department varchar(128),
  hr_office varchar(128),
  hr_telephone varchar(16),
  hr_mobile varchar(16),
  hr_image varchar(128),
  hr_hol_basic int4,
  hr_hol_adjust int4,
  hr_hol_unit varchar(1) DEFAULT 'D'
) INHERITS(sys_users) WITH OIDS;

I'll see if I can get a test example to work (==fail) with non-sensitive
data as soon as England have beaten Brazil... :-)

Regards, Dave.

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: 24 June 2002 00:45
> To: Dave Page
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] foreign Key problem
>
> > Anyway, here's a simple test case illustrating this problem.
> >
> > CREATE TABLE t1 (t1_id int4 PRIMARY KEY);
> > CREATE TABLE t2 (t2_id int4 PRIMARY KEY) INHERITS(t1);
> > CREATE TABLE t3 (t3_id int4 PRIMARY KEY);
> > CREATE TABLE t4 (
> >   t4_id1 int4,
> >   t4_id2 int4,
> >   CONSTRAINT t4_t1 FOREIGN KEY (t4_id1) REFERENCES t1 (t1_id) ON
> > DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
> >   CONSTRAINT t4_t3 FOREIGN KEY (t4_id2) REFERENCES t3 (t3_id) ON
> > DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY
> IMMEDIATE );
> >
> > INSERT INTO t2 (t1_id, t2_id) VALUES (1, 2);
> > INSERT INTO t3 (t3_id) VALUES (3);
> > INSERT INTO t4 (t4_id1, t4_id2) VALUES (1, 3);
> >
> > The error also occurs in 2 day old 7.3 dev code.
>
> Foreign keys don't inherit across tables currently, so when
> t4 references t1, it references *only* t1.  It won't see rows
> from t2. Alot of things don't inherit "properly" right now
> (also note that the primary key on t1_id doesn't inherit so,
> you don't even have guaranteed uniqueness across t1_id). In
> general inheritance needs some work.

Yes, that was roughly the conclusion that I've been coming to over the
weekend. It's annoying 'cos I always thought that inheritance was one of
the big PostgreSQL pluses and the first time I find a good use for the
feature it fails me bigtime :-(

Oh well, thanks for the help anyway.

Regards, Dave.

On Fri, 21 Jun 2002, Dave Page wrote:

> Following up my own message...
>
> > -----Original Message-----
> > From: Dave Page
> > Sent: 21 June 2002 08:29
> > To: 'Stephan Szabo'
> > Cc: pgsql-general@postgresql.org
> > Subject: RE: [GENERAL] foreign Key problem
> >
> >
> > I'll see if I can get a test example to work (==fail) with
> > non-sensitive data as soon as England have beaten Brazil... :-)
> >
>
> Oh well, you can't win 'em all :-(
>
> Anyway, here's a simple test case illustrating this problem.
>
> CREATE TABLE t1 (t1_id int4 PRIMARY KEY);
> CREATE TABLE t2 (t2_id int4 PRIMARY KEY) INHERITS(t1);
> CREATE TABLE t3 (t3_id int4 PRIMARY KEY);
> CREATE TABLE t4 (
>   t4_id1 int4,
>   t4_id2 int4,
>   CONSTRAINT t4_t1 FOREIGN KEY (t4_id1) REFERENCES t1 (t1_id) ON DELETE
> CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
>   CONSTRAINT t4_t3 FOREIGN KEY (t4_id2) REFERENCES t3 (t3_id) ON DELETE
> CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
> );
>
> INSERT INTO t2 (t1_id, t2_id) VALUES (1, 2);
> INSERT INTO t3 (t3_id) VALUES (3);
> INSERT INTO t4 (t4_id1, t4_id2) VALUES (1, 3);
>
> The error also occurs in 2 day old 7.3 dev code.

Foreign keys don't inherit across tables currently, so when t4 references
t1, it references *only* t1.  It won't see rows from t2. Alot of things
don't inherit "properly" right now (also note that the primary key on
t1_id doesn't inherit so, you don't even have guaranteed uniqueness
across t1_id). In general inheritance needs some work.

On Mon, 24 Jun 2002, Dave Page wrote:

> > Foreign keys don't inherit across tables currently, so when
> > t4 references t1, it references *only* t1.  It won't see rows
> > from t2. Alot of things don't inherit "properly" right now
> > (also note that the primary key on t1_id doesn't inherit so,
> > you don't even have guaranteed uniqueness across t1_id). In
> > general inheritance needs some work.
>
> Yes, that was roughly the conclusion that I've been coming to over the
> weekend. It's annoying 'cos I always thought that inheritance was one of
> the big PostgreSQL pluses and the first time I find a good use for the
> feature it fails me bigtime :-(

Inheritance needs a few champions who are willing to put time into making
sure that it gets to the point where it has all these features or at
least to help spec out what all these features should do.

As a side note, if you're willing to do a few extra inserts, there are
some workarounds for the foreign key case involving another table
where you get ids from there and reference that table from each of the
tables in the hierarchy and tables that want to reference the hierarchy
reference that table.

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: 24 June 2002 16:15
> To: Dave Page
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] foreign Key problem
>
> As a side note, if you're willing to do a few extra inserts,
> there are some workarounds for the foreign key case involving
> another table where you get ids from there and reference that
> table from each of the tables in the hierarchy and tables
> that want to reference the hierarchy reference that table.
>

Hmm, the major reason for wanting the foreign key is maintain integrity
between sys_users & dms_acl. It'll probably be easiest for me to just
remove that foreign key and ensure the app deletes matching acl records
when a particular user is deleted. At least then I can just add the fkey
when/if PostgreSQL gets fixed and won't have a mess of a design left
over from a workaround.

Thanks anyway,

Dave.