Re: FOREIGN KEY Reference on multiple columns - Mailing list pgsql-sql

From Luca Vernini
Subject Re: FOREIGN KEY Reference on multiple columns
Date
Msg-id CAHZ=uVAEJ=dKbwOKjYhmGQzcyJ3BSWrtgSjy0qLgbdoKOSREWg@mail.gmail.com
Whole thread Raw
In response to Re: FOREIGN KEY Reference on multiple columns  (Weiss, Jörg <J.Weiss@dvz-mv.de>)
Responses Re: FOREIGN KEY Reference on multiple columns
List 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



pgsql-sql by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: FOREIGN KEY Reference on multiple columns
Next
From: Weiss, Jörg
Date:
Subject: Re: FOREIGN KEY Reference on multiple columns