agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFOREIGN KEY Reference on multiple columns
10+ messages / 4 participants
[nested] [flat]
* FOREIGN KEY Reference on multiple columns
@ 2014-09-23 09:54 Weiss, Jörg <J.Weiss@dvz-mv.de>
0 siblings, 1 reply; 10+ messages in thread
From: Weiss, Jörg @ 2014-09-23 09:54 UTC (permalink / raw)
To: pgsql-sql
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 ...
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: FOREIGN KEY Reference on multiple columns
@ 2014-09-23 09:59 Luca Vernini <lucazeo@gmail.com>
parent: Weiss, Jörg <J.Weiss@dvz-mv.de>
0 siblings, 1 reply; 10+ messages in thread
From: Luca Vernini @ 2014-09-23 09:59 UTC (permalink / raw)
To: Weiss, Jörg <J.Weiss@dvz-mv.de>; +Cc: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: FOREIGN KEY Reference on multiple columns
@ 2014-09-23 11:38 Weiss, Jörg <J.Weiss@dvz-mv.de>
parent: Luca Vernini <lucazeo@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Weiss, Jörg @ 2014-09-23 11:38 UTC (permalink / raw)
To: pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: FOREIGN KEY Reference on multiple columns
@ 2014-09-23 12:36 Luca Vernini <lucazeo@gmail.com>
parent: Weiss, Jörg <J.Weiss@dvz-mv.de>
1 sibling, 1 reply; 10+ messages in thread
From: Luca Vernini @ 2014-09-23 12:36 UTC (permalink / raw)
To: Weiss, Jörg <J.Weiss@dvz-mv.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: FOREIGN KEY Reference on multiple columns
@ 2014-09-23 13:10 Weiss, Jörg <J.Weiss@dvz-mv.de>
parent: Luca Vernini <lucazeo@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Weiss, Jörg @ 2014-09-23 13:10 UTC (permalink / raw)
To: 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,
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: FOREIGN KEY Reference on multiple columns
@ 2014-09-23 13:15 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Weiss, Jörg <J.Weiss@dvz-mv.de>
1 sibling, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2014-09-23 13:15 UTC (permalink / raw)
To: "Weiss, Jörg" <J.Weiss@dvz-mv.de>; 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: FOREIGN KEY Reference on multiple columns
@ 2014-09-23 13:17 Luca Vernini <lucazeo@gmail.com>
parent: Weiss, Jörg <J.Weiss@dvz-mv.de>
1 sibling, 1 reply; 10+ messages in thread
From: Luca Vernini @ 2014-09-23 13:17 UTC (permalink / raw)
To: Weiss, Jörg <J.Weiss@dvz-mv.de>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: FOREIGN KEY Reference on multiple columns
@ 2014-09-23 13:38 Weiss, Jörg <J.Weiss@dvz-mv.de>
parent: Luca Vernini <lucazeo@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Weiss, Jörg @ 2014-09-23 13:38 UTC (permalink / raw)
To: 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 combination of 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: FOREIGN KEY Reference on multiple columns
@ 2014-09-24 18:02 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Weiss, Jörg <J.Weiss@dvz-mv.de>
1 sibling, 1 reply; 10+ messages in thread
From: Tim Landscheidt @ 2014-09-24 18:02 UTC (permalink / raw)
To: 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: FOREIGN KEY Reference on multiple columns
@ 2014-09-25 05:04 Weiss, Jörg <J.Weiss@dvz-mv.de>
parent: Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 0 replies; 10+ messages in thread
From: Weiss, Jörg @ 2014-09-25 05:04 UTC (permalink / raw)
To: pgsql-sql
> -----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!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2014-09-25 05:04 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-09-23 09:54 FOREIGN KEY Reference on multiple columns Weiss, Jörg <J.Weiss@dvz-mv.de>
2014-09-23 09:59 ` Luca Vernini <lucazeo@gmail.com>
2014-09-23 11:38 ` Weiss, Jörg <J.Weiss@dvz-mv.de>
2014-09-23 12:36 ` Luca Vernini <lucazeo@gmail.com>
2014-09-23 13:10 ` Weiss, Jörg <J.Weiss@dvz-mv.de>
2014-09-23 13:17 ` Luca Vernini <lucazeo@gmail.com>
2014-09-23 13:38 ` Weiss, Jörg <J.Weiss@dvz-mv.de>
2014-09-24 18:02 ` Tim Landscheidt <tim@tim-landscheidt.de>
2014-09-25 05:04 ` Weiss, Jörg <J.Weiss@dvz-mv.de>
2014-09-23 13:15 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox