Обсуждение: FOREIGN KEY Reference on multiple columns
Hi!
Is it possible to create a “FOREIGN KEY CONSTRAINT” with references to multiple columns of the reference table?
For example:
Table parm:
CREATE TABLE parm
(
complex varchar(20) NOT NULL,
para varchar(50) NOT NULL,
sort int4 NOT NULL DEFAULT 10,
value varchar(50) NULL,
CONSTRAINT parm_pkey PRIMARY KEY (complex, para, sort)
)
Table user
CREATE TABLE user
(
name varchar(20) NOT NULL,
type integer NULL
)
Now I want to create FOREIGN KEY on user.type with references on parm.value and param.para WHERE param.para = ‘user_type’
Something like this:
ALTER TABLE user ADD CONSTRAINT user_type_fkey FOREIGN KEY (type) REFERENCES parm (value,para) WHERE parm.para = 'user_type';
Regards …
2014-09-23 11:54 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: > Hi! Hi. > Is it possible to create a “FOREIGN KEY CONSTRAINT” with references to > multiple columns of the reference table? Yes, it is. see here: http://www.postgresql.org/docs/9.3/static/ddl-constraints.html Section 5.3.5 there are some examples. One of them has multiple columns, just like your case. > Regards … Regards.
> -----Ursprüngliche Nachricht----- > Von: Luca Vernini [mailto:lucazeo@gmail.com] > Gesendet: Dienstag, 23. September 2014 11:59 > An: Weiss, Jörg > Cc: pgsql-sql@postgresql.org > Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns > > 2014-09-23 11:54 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: > > Hi! > Hi. > > > Is it possible to create a “FOREIGN KEY CONSTRAINT” with references to > > multiple columns of the reference table? > > Yes, it is. > > see here: > http://www.postgresql.org/docs/9.3/static/ddl-constraints.html > Section 5.3.5 there are some examples. One of them has multiple > columns, just like your case. > > > Regards … > > Regards. Thank you! Do you mean this example? CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) ); This is not, what I want. In my case it should be something like this: CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') ); This example does not work. But I think you can see, what I mean.
Sorry, I don't get your problem. In first example there was a where, in second case: FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') You mean b must be equal to what? Try to include real table script. Probably you can use a check. Regards, Luca. 2014-09-23 13:38 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: >> -----Ursprüngliche Nachricht----- >> Von: Luca Vernini [mailto:lucazeo@gmail.com] >> Gesendet: Dienstag, 23. September 2014 11:59 >> An: Weiss, Jörg >> Cc: pgsql-sql@postgresql.org >> Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns >> >> 2014-09-23 11:54 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: >> > Hi! >> Hi. >> >> > Is it possible to create a “FOREIGN KEY CONSTRAINT” with references to >> > multiple columns of the reference table? >> >> Yes, it is. >> >> see here: >> http://www.postgresql.org/docs/9.3/static/ddl-constraints.html >> Section 5.3.5 there are some examples. One of them has multiple >> columns, just like your case. >> >> > Regards … >> >> Regards. > > Thank you! > > Do you mean this example? > CREATE TABLE t1 ( > a integer PRIMARY KEY, > b integer, > c integer, > FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) > ); > This is not, what I want. > > In my case it should be something like this: > CREATE TABLE t1 ( > a integer PRIMARY KEY, > b integer, > c integer, > FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') > ); > This example does not work. But I think you can see, what I mean. > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
I mean b must equal to c1 in the "other_table" where c2 has a certain value (for example c2 ). For my first example: CREATE TABLE parm ( complex varchar(20) NOT NULL, para varchar(50) NOT NULL, sort int4 NOT NULL DEFAULT 10, value varchar(50) NULL,CONSTRAINTparm_pkey PRIMARY KEY (complex, para, sort) ) Table user CREATE TABLE user ( name varchar(20) NOT NULL, type integer NULL ) In this case "type" of table user must equal to "value" of table "parm" and "para" must be "login_user" (for example) Regards Jörg > -----Ursprüngliche Nachricht----- > Von: Luca Vernini [mailto:lucazeo@gmail.com] > Gesendet: Dienstag, 23. September 2014 14:37 > An: Weiss, Jörg > Cc: pgsql-sql@postgresql.org > Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns > > Sorry, I don't get your problem. > In first example there was a where, in second case: > FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') > You mean b must be equal to what? > Try to include real table script. > Probably you can use a check. > > Regards, > > Luca. > > 2014-09-23 13:38 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: > >> -----Ursprüngliche Nachricht----- > >> Von: Luca Vernini [mailto:lucazeo@gmail.com] > >> Gesendet: Dienstag, 23. September 2014 11:59 > >> An: Weiss, Jörg > >> Cc: pgsql-sql@postgresql.org > >> Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns > >> > >> 2014-09-23 11:54 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: > >> > Hi! > >> Hi. > >> > >> > Is it possible to create a “FOREIGN KEY CONSTRAINT” with references to > >> > multiple columns of the reference table? > >> > >> Yes, it is. > >> > >> see here: > >> http://www.postgresql.org/docs/9.3/static/ddl-constraints.html > >> Section 5.3.5 there are some examples. One of them has multiple > >> columns, just like your case. > >> > >> > Regards … > >> > >> Regards. > > > > Thank you! > > > > Do you mean this example? > > CREATE TABLE t1 ( > > a integer PRIMARY KEY, > > b integer, > > c integer, > > FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) > > ); > > This is not, what I want. > > > > In my case it should be something like this: > > CREATE TABLE t1 ( > > a integer PRIMARY KEY, > > b integer, > > c integer, > > FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') > > ); > > This example does not work. But I think you can see, what I mean. > > > > > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql
On 09/23/2014 04:38 AM, Weiss, Jörg wrote: >> -----Ursprüngliche Nachricht----- >> Von: Luca Vernini [mailto:lucazeo@gmail.com] >> Gesendet: Dienstag, 23. September 2014 11:59 >> An: Weiss, Jörg >> Cc: pgsql-sql@postgresql.org >> Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns >> >> 2014-09-23 11:54 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: >>> Hi! >> Hi. >> >>> Is it possible to create a “FOREIGN KEY CONSTRAINT” with references to >>> multiple columns of the reference table? >> >> Yes, it is. >> >> see here: >> http://www.postgresql.org/docs/9.3/static/ddl-constraints.html >> Section 5.3.5 there are some examples. One of them has multiple >> columns, just like your case. >> >>> Regards … >> >> Regards. > > Thank you! > > Do you mean this example? > CREATE TABLE t1 ( > a integer PRIMARY KEY, > b integer, > c integer, > FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) > ); > This is not, what I want. > > In my case it should be something like this: > CREATE TABLE t1 ( > a integer PRIMARY KEY, > b integer, > c integer, > FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') > ); > This example does not work. But I think you can see, what I mean. Foreign keys do not support the when clause. In your original example there is also the problem that value is not part of a unique index or the primary key. > > > -- Adrian Klaver adrian.klaver@aklaver.com
I see. You cannot do a constraint with a "certain value", if I understood right. This is not even possible in any know rdbms, because a constraint must reference something stored on tables. Persisted on db. You can only check values while inserting. I suggest useing a function. Regards, Luca. 2014-09-23 15:10 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: > I mean b must equal to c1 in the "other_table" where c2 has a certain value (for example c2 ). > > For my first example: > CREATE TABLE parm > ( > complex varchar(20) NOT NULL, > para varchar(50) NOT NULL, > sort int4 NOT NULL DEFAULT 10, > value varchar(50) NULL, > CONSTRAINT parm_pkey PRIMARY KEY (complex, para, sort) > ) > > Table user > CREATE TABLE user > ( > name varchar(20) NOT NULL, > type integer NULL > ) > In this case "type" of table user must equal to "value" of table "parm" and "para" must be "login_user" (for example) > > Regards > > Jörg > >> -----Ursprüngliche Nachricht----- >> Von: Luca Vernini [mailto:lucazeo@gmail.com] >> Gesendet: Dienstag, 23. September 2014 14:37 >> An: Weiss, Jörg >> Cc: pgsql-sql@postgresql.org >> Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns >> >> Sorry, I don't get your problem. >> In first example there was a where, in second case: >> FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') >> You mean b must be equal to what? >> Try to include real table script. >> Probably you can use a check. >> >> Regards, >> >> Luca. >> >> 2014-09-23 13:38 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: >> >> -----Ursprüngliche Nachricht----- >> >> Von: Luca Vernini [mailto:lucazeo@gmail.com] >> >> Gesendet: Dienstag, 23. September 2014 11:59 >> >> An: Weiss, Jörg >> >> Cc: pgsql-sql@postgresql.org >> >> Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns >> >> >> >> 2014-09-23 11:54 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: >> >> > Hi! >> >> Hi. >> >> >> >> > Is it possible to create a “FOREIGN KEY CONSTRAINT” with references to >> >> > multiple columns of the reference table? >> >> >> >> Yes, it is. >> >> >> >> see here: >> >> http://www.postgresql.org/docs/9.3/static/ddl-constraints.html >> >> Section 5.3.5 there are some examples. One of them has multiple >> >> columns, just like your case. >> >> >> >> > Regards … >> >> >> >> Regards. >> > >> > Thank you! >> > >> > Do you mean this example? >> > CREATE TABLE t1 ( >> > a integer PRIMARY KEY, >> > b integer, >> > c integer, >> > FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) >> > ); >> > This is not, what I want. >> > >> > In my case it should be something like this: >> > CREATE TABLE t1 ( >> > a integer PRIMARY KEY, >> > b integer, >> > c integer, >> > FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') >> > ); >> > This example does not work. But I think you can see, what I mean. >> > >> > >> > >> > -- >> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> > To make changes to your subscription: >> > http://www.postgresql.org/mailpref/pgsql-sql > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
That's what I wanted to know. It's not only a constraint to a "certain value" or (better) "specific value", it should be a constraint to a combinationof the values of 2 columns. "type" of table "user" must equal to "value" of table "parm" where "para" of table "parm" must always be "login_user". Check or a function is not the same. A "foreign key" works on both sides. Table "user" and table "parm". OK I can do checks at both tables ON UPDATE, INSERT or DELETE. This may work. Regards Jörg > -----Ursprüngliche Nachricht----- > Von: Luca Vernini [mailto:lucazeo@gmail.com] > Gesendet: Dienstag, 23. September 2014 15:18 > An: Weiss, Jörg > Cc: pgsql-sql@postgresql.org > Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns > > I see. > You cannot do a constraint with a "certain value", if I understood > right. This is not even possible in any know rdbms, because a > constraint must reference something stored on tables. Persisted on db. > You can only check values while inserting. I suggest useing a function. > > Regards, > > Luca. > > 2014-09-23 15:10 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: > > I mean b must equal to c1 in the "other_table" where c2 has a certain value > (for example c2 ). > > > > For my first example: > > CREATE TABLE parm > > ( > > complex varchar(20) NOT NULL, > > para varchar(50) NOT NULL, > > sort int4 NOT NULL DEFAULT 10, > > value varchar(50) NULL, > > CONSTRAINT parm_pkey PRIMARY KEY (complex, para, sort) > > ) > > > > Table user > > CREATE TABLE user > > ( > > name varchar(20) NOT NULL, > > type integer NULL > > ) > > In this case "type" of table user must equal to "value" of table "parm" and > "para" must be "login_user" (for example) > > > > Regards > > > > Jörg > > > >> -----Ursprüngliche Nachricht----- > >> Von: Luca Vernini [mailto:lucazeo@gmail.com] > >> Gesendet: Dienstag, 23. September 2014 14:37 > >> An: Weiss, Jörg > >> Cc: pgsql-sql@postgresql.org > >> Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns > >> > >> Sorry, I don't get your problem. > >> In first example there was a where, in second case: > >> FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') > >> You mean b must be equal to what? > >> Try to include real table script. > >> Probably you can use a check. > >> > >> Regards, > >> > >> Luca. > >> > >> 2014-09-23 13:38 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: > >> >> -----Ursprüngliche Nachricht----- > >> >> Von: Luca Vernini [mailto:lucazeo@gmail.com] > >> >> Gesendet: Dienstag, 23. September 2014 11:59 > >> >> An: Weiss, Jörg > >> >> Cc: pgsql-sql@postgresql.org > >> >> Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns > >> >> > >> >> 2014-09-23 11:54 GMT+02:00 Weiss, Jörg <J.Weiss@dvz-mv.de>: > >> >> > Hi! > >> >> Hi. > >> >> > >> >> > Is it possible to create a “FOREIGN KEY CONSTRAINT” with references > to > >> >> > multiple columns of the reference table? > >> >> > >> >> Yes, it is. > >> >> > >> >> see here: > >> >> http://www.postgresql.org/docs/9.3/static/ddl-constraints.html > >> >> Section 5.3.5 there are some examples. One of them has multiple > >> >> columns, just like your case. > >> >> > >> >> > Regards … > >> >> > >> >> Regards. > >> > > >> > Thank you! > >> > > >> > Do you mean this example? > >> > CREATE TABLE t1 ( > >> > a integer PRIMARY KEY, > >> > b integer, > >> > c integer, > >> > FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) > >> > ); > >> > This is not, what I want. > >> > > >> > In my case it should be something like this: > >> > CREATE TABLE t1 ( > >> > a integer PRIMARY KEY, > >> > b integer, > >> > c integer, > >> > FOREIGN KEY (b) REFERENCES other_table (c1, c2='c2Value') > >> > ); > >> > This example does not work. But I think you can see, what I mean. > >> > > >> > > >> > > >> > -- > >> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > >> > To make changes to your subscription: > >> > http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql
"Weiss, Jörg" <J.Weiss@dvz-mv.de> wrote: > I mean b must equal to c1 in the "other_table" where c2 has a certain value (for example c2 ). > For my first example: > CREATE TABLE parm > ( > complex varchar(20) NOT NULL, > para varchar(50) NOT NULL, > sort int4 NOT NULL DEFAULT 10, > value varchar(50) NULL, > CONSTRAINT parm_pkey PRIMARY KEY (complex, para, sort) > ) > Table user > CREATE TABLE user > ( > name varchar(20) NOT NULL, > type integer NULL > ) > In this case "type" of table user must equal to "value" of table "parm" and "para" must be "login_user" (for example) > [...] You can achieve that by duplicating the para column to the table user, adding a foreign key that matches both columns to table parm and checks in table user whether para is "login_user". That doesn't work for NULLable columns, though. Tim
> -----Ursprüngliche Nachricht----- > Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql- > owner@postgresql.org] Im Auftrag von Tim Landscheidt > Gesendet: Mittwoch, 24. September 2014 20:03 > An: pgsql-sql@postgresql.org > Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns > > "Weiss, Jörg" <J.Weiss@dvz-mv.de> wrote: > > > I mean b must equal to c1 in the "other_table" where c2 has a certain value > (for example c2 ). > > > For my first example: > > CREATE TABLE parm > > ( > > complex varchar(20) NOT NULL, > > para varchar(50) NOT NULL, > > sort int4 NOT NULL DEFAULT 10, > > value varchar(50) NULL, > > CONSTRAINT parm_pkey PRIMARY KEY (complex, para, sort) > > ) > > > Table user > > CREATE TABLE user > > ( > > name varchar(20) NOT NULL, > > type integer NULL > > ) > > In this case "type" of table user must equal to "value" of table "parm" and > "para" must be "login_user" (for example) > > > [...] > > You can achieve that by duplicating the para column to the > table user, adding a foreign key that matches both columns > to table parm and checks in table user whether para is > "login_user". That doesn't work for NULLable columns, > though. > > Tim > Yes of cause, this seems to be the best way. Thank You!