Problem between inheritance and references

On Tue, 23 Jul 2002, Stephane Bortzmeyer wrote:

I'm just beginning with inheritance and I have problems using
REFERENCES integrity constraints to an inherited table. The schema is
attached. When I try the second INSERT, I get:

psql:./auth.sql:22: ERROR: <unnamed> referential integrity violation
- key referenced from users not found in authentication

But the key does exist:

The problem is that foreign keys, primary keys, unique constraints,
and triggers (and maybe other stuff, but at least that) don't inherit,
so the foreign key is referencing only the table mentioned. The
equivalent select is select * from ONLY Authentication I believe.

On Tue, Jul 23, 2002 at 08:11:30AM -0700,
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote
a message of 16 lines which said:

The problem is that foreign keys, primary keys, unique constraints,
and triggers (and maybe other stuff, but at least that) don't
inherit,

:-( So basically, INHERITS just give you the columns of the parent
table?

so the foreign key is referencing only the table mentioned. The
equivalent select is select * from ONLY Authentication I believe.

OK, I see the problem. Any suggestion on how to rephrase my schema
(besides dropping inheritance)?

On Tue, 23 Jul 2002, Stephane Bortzmeyer wrote:

On Tue, Jul 23, 2002 at 08:11:30AM -0700,
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote
a message of 16 lines which said:

The problem is that foreign keys, primary keys, unique constraints,
and triggers (and maybe other stuff, but at least that) don't
inherit,

:-( So basically, INHERITS just give you the columns of the parent
table?

Well, you get check constraints as well AFAIR. Doing the rest of the
above is something that will probably get done eventually, but it
could definately use some work by the people that are using it on
a regular basis.

so the foreign key is referencing only the table mentioned. The
equivalent select is select * from ONLY Authentication I believe.

OK, I see the problem. Any suggestion on how to rephrase my schema
(besides dropping inheritance)?

Umm, in general the only way I've heard of was doing a separate table
that stores the official value of the keys that has the inheritance
tree referencing it (each table therein) and outside references go
to that table as well. That also guarantees uniqueness which isn't
guaranteed if you have separate unique constraints on each table.

On Tue, 23 Jul 2002, Stephane Bortzmeyer wrote:

OK, I see the problem. Any suggestion on how to rephrase my schema
(besides dropping inheritance)?

What would be the disadvantage of dropping inheritance, and just
using standard relational methods (foreign key, join, etc.)?

cjs
--
Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

On Wed, Jul 24, 2002 at 11:11:25AM +0900,
Curt Sampson <cjs@cynic.net> wrote
a message of 12 lines which said:

OK, I see the problem. Any suggestion on how to rephrase my schema
(besides dropping inheritance)?

What would be the disadvantage of dropping inheritance, and just
using standard relational methods (foreign key, join, etc.)?

It's less beautiful :-( Anyway, here is my new schema, which seems to work:

CREATE TABLE Authentication (
id SERIAL,
method TEXT NOT NULL CHECK (method IN ('GPG', 'X509', 'password')),
password TEXT,
gpgpubkey TEXT,
certificate TEXT,
CHECK (password IS NOT NULL OR gpgpubkey IS NOT NULL OR certificate IS NOT NULL));

CREATE TABLE Users (
id SERIAL,
name TEXT UNIQUE NOT NULL,
auth_info INTEGER REFERENCES Authentication (id));

On Tue, Jul 23, 2002 at 09:01:08AM -0700,
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote
a message of 29 lines which said:

Well, you get check constraints as well AFAIR. Doing the rest of the
above is something that will probably get done eventually, but it
could definately use some work by the people that are using it on
a regular basis.

OK :-) My knowledge of PostgreSQL's internals is too modest for me to
help. I changed my schema and forgot about inheritance and I'll wait
without holding my breath :-)