Re: FOREIGN KEY Reference on multiple columns - Mailing list 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