Обсуждение: Problem between inheritance and references
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: auth=# select * from Authentication; id ---- 1 (1 row) auth=# select * from Passwords; id | password ----+------------ 1 | trustnoone (1 row)
Вложения
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 :-)