Обсуждение: FOREIGN KEY Reference on multiple columns
<div class="WordSection1"><p class="MsoNormal">Hi!<p class="MsoNormal"> <p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">Isit possible to create a “FOREIGN KEY CONSTRAINT” with references to multiple columns ofthe reference table?</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"> </span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US">For example:</span><p class="MsoNormal"><span lang="EN-US"style="mso-ansi-language:EN-US">Table <span class="SpellE"> parm</span>:</span><p class="MsoNormal"><span lang="EN-US"style="mso-ansi-language:EN-US">CREATE TABLE <span class="SpellE">parm</span></span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US">(</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><spanstyle="mso-spacerun:yes"> </span>complex <span class="SpellE">varchar</span>(20) NOTNULL,</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><span style="mso-spacerun:yes"> </span>para<span class="SpellE">varchar</span>(50) NOT NULL,</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><spanstyle="mso-spacerun:yes"> </span>sort <span class="SpellE">int4</span> NOT NULL DEFAULT10,</span><p class="MsoNormal"><span style="mso-spacerun:yes"> </span><span class="SpellE">value</span> <span class="SpellE">varchar</span>(50)NULL,<span lang="EN-US" style="mso-ansi-language:EN-US"></span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US"><span style="mso-spacerun:yes"> </span>CONSTRAINT <spanclass="SpellE">parm_pkey</span> PRIMARY KEY (complex, para, sort)</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">)</span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"> </span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">Table user</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">CREATE TABLE user</span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US">(</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><spanstyle="mso-spacerun:yes"> </span>name <span class="SpellE">varchar</span>(20) NOT NULL,</span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"><span style="mso-spacerun:yes"> </span>typeinteger NULL</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">)</span><p class="MsoNormal"><spanlang="EN-US" style="mso-ansi-language:EN-US"> </span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">NowI want to create FOREIGN KEY on <span class="SpellE">user.type</span> with referenceson <span class="SpellE">parm.value</span> and <span class="SpellE">param.para</span> WHERE <span class="SpellE">param.para</span>= ‘<span class="SpellE">user_type</span>’</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">Somethinglike this:</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">ALTERTABLE user ADD CONSTRAINT <span class="SpellE">user_type_fkey</span> FOREIGN KEY (type)REFERENCES <span class="SpellE"> parm</span> (<span class="SpellE">value,para</span>) WHERE <span class="SpellE">parm.para</span>= '<span class="SpellE">user_type</span>';</span><p class="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"> </span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US">Regards …</span><pclass="MsoNormal"><span lang="EN-US" style="mso-ansi-language:EN-US"> </span><p class="MsoNormal"><span lang="EN-US"style="mso-ansi-language:EN-US"> </span></div>
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!