From J.Weiss@dvz-mv.de Tue Sep 23 09:54:25 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWMnh-000468-4w for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 09:54:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWMng-0006oB-G1 for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 09:54:24 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWMnf-0006o4-2k for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 09:54:23 +0000 Received: from vmailinpx1.mvnet.de ([194.25.109.14]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XWMnb-00067M-30 for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 09:54:21 +0000 Received: from vmgwpa1.mvnet.de (fw.mvnet.de [195.145.109.44]) by vmailinpx1.mvnet.de (/) with ESMTP id s8N9sGH5028674 for ; Tue, 23 Sep 2014 11:54:16 +0200 Received: from smtp.bk.dvz-mv.net ([10.254.1.150]) by vmgwpa1.mvnet.de (/) with ESMTP id s8N9sFJM046734 for ; Tue, 23 Sep 2014 11:54:15 +0200 Received: from DVZSN-RA0325.bk.dvz-mv.net ([169.254.2.121]) by DVZSN-RA0323.bk.dvz-mv.net ([169.254.1.228]) with mapi id 14.02.0387.000; Tue, 23 Sep 2014 11:54:15 +0200 From: =?iso-8859-1?Q?Weiss=2C_J=F6rg?= To: "pgsql-sql@postgresql.org" Subject: FOREIGN KEY Reference on multiple columns Thread-Topic: FOREIGN KEY Reference on multiple columns Thread-Index: Ac/XEc5cK2GmPw+fQPCw4xE5O67V5g== Date: Tue, 23 Sep 2014 09:54:15 +0000 Message-ID: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.4.81.101] x-pmwin-version: 3.1.2.0, Antivirus-Engine: 3.51.1, Antivirus-Data: 5.06 Content-Type: multipart/alternative; boundary="_000_4B4E89127868BD458A795430BCF4FD1328C51A6FDVZSNRA0325bkdv_" MIME-Version: 1.0 X-Virus-Scanned: ClamAV using ClamSMTP X-Pg-Spam-Score: 0.9 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_000_4B4E89127868BD458A795430BCF4FD1328C51A6FDVZSNRA0325bkdv_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi! Is it possible to create a "FOREIGN KEY CONSTRAINT" with references to mult= iple 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 =3D 'user_type' Something like this: ALTER TABLE user ADD CONSTRAINT user_type_fkey FOREIGN KEY (type) REFERENCE= S parm (value,para) WHERE parm.para =3D 'user_type'; Regards ... --_000_4B4E89127868BD458A795430BCF4FD1328C51A6FDVZSNRA0325bkdv_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi!

 

Is it possible to create a “FOREIGN KEY CONSTRAINT” with ref= erences to multiple columns of the reference table?

 

For example:

Table parm:

CREATE TABLE parm

(

  complex varchar(20) NOT NULL,

  para varchar(50) NOT NULL,<= /span>

  sort int4 NOT NULL DEFAULT 10,<= o:p>

  value varchar(50) NULL,

 CONSTRAINT parm_pkey PRIMARY KEY (complex, para, sort)

)

 

Table user

CREATE TABLE user

(

  name varchar(20) NOT NULL,<= /span>

  type integer NULL

)

 

Now I want to create FOREIGN KEY on user.type with references on parm.value and param.para WHERE param= .para =3D ‘user_type’=

Something like this:

ALTER TABLE user ADD CONSTRAINT user_type_fkey FOREIGN KEY (type) REFERENCES = parm (value,para) WHERE parm.para =3D 'user_type';

 

Regards …

 

 

--_000_4B4E89127868BD458A795430BCF4FD1328C51A6FDVZSNRA0325bkdv_-- From lucazeo@gmail.com Tue Sep 23 09:59:30 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWMsc-0004L5-4J for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 09:59:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWMsb-0007zV-Ft for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 09:59:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWMsa-0007zO-Bq for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 09:59:28 +0000 Received: from mail-la0-x22b.google.com ([2a00:1450:4010:c03::22b]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XWMsX-0007x9-3y for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 09:59:27 +0000 Received: by mail-la0-f43.google.com with SMTP id gi9so8329958lab.2 for ; Tue, 23 Sep 2014 02:59:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; bh=0xKQmfNMIak2HQ7poi6oCPohNdCjbViaK4ZE8d/MF80=; b=b8rU5fbzqUjyXAGI+TPdjuA2H525nCUmqPGHsrmLlgOtunUo/XIyn16df0sv9uDJZ5 EtvCehgB3EmnXysUeiOwooI0Q8MYqMZ+1xo1rREHS8YJB3J+V7a/R412/TrWg+YO0FCf 0w4y4Sed6tEGJwkPapweTDbrB5ad/cBknnXw6ZdDr2z0gMVnVBwe6a+/aukgw5hcWxyL nq61q4U4HWHgL/FuStlCpZRVAMWb+1Vq24hlv2JoAx/3LTqvcdhMIdTEL4BoW99VrOGT NFzBE6DRYVX51pH71gIGVdFOlYkdr3qTnmno30gZGKSzDfumCkJ/SnYxDH5CX7QO9+k9 EUEg== MIME-Version: 1.0 X-Received: by 10.152.21.136 with SMTP id v8mr31697645lae.41.1411466362400; Tue, 23 Sep 2014 02:59:22 -0700 (PDT) Received: by 10.25.126.4 with HTTP; Tue, 23 Sep 2014 02:59:22 -0700 (PDT) In-Reply-To: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> References: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> Date: Tue, 23 Sep 2014 11:59:22 +0200 Message-ID: Subject: Re: FOREIGN KEY Reference on multiple columns From: Luca Vernini To: =?UTF-8?B?V2Vpc3MsIErDtnJn?= Cc: "pgsql-sql@postgresql.org" Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org 2014-09-23 11:54 GMT+02:00 Weiss, J=C3=B6rg : > Hi! Hi. > Is it possible to create a =E2=80=9CFOREIGN KEY CONSTRAINT=E2=80=9D 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 =E2=80=A6 Regards. --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From J.Weiss@dvz-mv.de Tue Sep 23 11:38:28 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWOQO-0000Ik-Jw for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 11:38:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWOQN-0007X0-JV for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 11:38:27 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWOQM-0007Wa-PM for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 11:38:26 +0000 Received: from vmailinpx1.mvnet.de ([194.25.109.14]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XWOQA-00081z-Mk for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 11:38:25 +0000 Received: from vmgwpa1.mvnet.de (fw.mvnet.de [195.145.109.44]) by vmailinpx1.mvnet.de (/) with ESMTP id s8NBcB8m008629 for ; Tue, 23 Sep 2014 13:38:11 +0200 Received: from smtp.bk.dvz-mv.net ([10.254.1.150]) by vmgwpa1.mvnet.de (/) with ESMTP id s8NBcAZA014911 for ; Tue, 23 Sep 2014 13:38:11 +0200 Received: from DVZSN-RA0325.bk.dvz-mv.net ([169.254.2.121]) by DVZSN-RA0323.bk.dvz-mv.net ([169.254.1.228]) with mapi id 14.02.0387.000; Tue, 23 Sep 2014 13:38:10 +0200 From: =?utf-8?B?V2Vpc3MsIErDtnJn?= To: "pgsql-sql@postgresql.org" Subject: Re: FOREIGN KEY Reference on multiple columns Thread-Topic: [SQL] FOREIGN KEY Reference on multiple columns Thread-Index: Ac/XEc5cK2GmPw+fQPCw4xE5O67V5v//5PUA///GunA= Date: Tue, 23 Sep 2014 11:38:10 +0000 Message-ID: <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> References: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> In-Reply-To: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.4.81.101] x-pmwin-version: 3.1.2.0, Antivirus-Engine: 3.51.1, Antivirus-Data: 5.06 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 MIME-Version: 1.0 X-Virus-Scanned: ClamAV using ClamSMTP X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org PiAtLS0tLVVyc3Byw7xuZ2xpY2hlIE5hY2hyaWNodC0tLS0tDQo+IFZvbjog THVjYSBWZXJuaW5pIFttYWlsdG86bHVjYXplb0BnbWFpbC5jb21dIA0KPiBH ZXNlbmRldDogRGllbnN0YWcsIDIzLiBTZXB0ZW1iZXIgMjAxNCAxMTo1OQ0K PiBBbjogV2Vpc3MsIErDtnJnDQo+IENjOiBwZ3NxbC1zcWxAcG9zdGdyZXNx bC5vcmcNCj4gQmV0cmVmZjogUmU6IFtTUUxdIEZPUkVJR04gS0VZIFJlZmVy ZW5jZSBvbiBtdWx0aXBsZSBjb2x1bW5zDQo+DQo+IDIwMTQtMDktMjMgMTE6 NTQgR01UKzAyOjAwIFdlaXNzLCBKw7ZyZyA8Si5XZWlzc0BkdnotbXYuZGU+ Og0KPiA+IEhpIQ0KPiBIaS4NCj4NCj4gPiBJcyBpdCBwb3NzaWJsZSB0byBj cmVhdGUgYSDigJxGT1JFSUdOIEtFWSBDT05TVFJBSU5U4oCdIHdpdGggcmVm ZXJlbmNlcyB0bw0KPiA+IG11bHRpcGxlIGNvbHVtbnMgb2YgdGhlIHJlZmVy ZW5jZSB0YWJsZT8NCj4NCj4gWWVzLCBpdCBpcy4NCj4NCj4gc2VlIGhlcmU6 DQo+IGh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvZG9jcy85LjMvc3RhdGlj L2RkbC1jb25zdHJhaW50cy5odG1sDQo+IFNlY3Rpb24gNS4zLjUgdGhlcmUg YXJlIHNvbWUgZXhhbXBsZXMuIE9uZSBvZiB0aGVtIGhhcyBtdWx0aXBsZQ0K PiBjb2x1bW5zLCBqdXN0IGxpa2UgeW91ciBjYXNlLg0KPg0KPiA+IFJlZ2Fy ZHMg4oCmDQo+DQo+IFJlZ2FyZHMuDQoNClRoYW5rIHlvdSENCg0KRG8geW91 IG1lYW4gdGhpcyBleGFtcGxlPw0KQ1JFQVRFIFRBQkxFIHQxICgNCiAgYSBp bnRlZ2VyIFBSSU1BUlkgS0VZLA0KICBiIGludGVnZXIsDQogIGMgaW50ZWdl ciwNCiAgRk9SRUlHTiBLRVkgKGIsIGMpIFJFRkVSRU5DRVMgb3RoZXJfdGFi bGUgKGMxLCBjMikNCik7DQpUaGlzIGlzIG5vdCwgd2hhdCBJIHdhbnQuDQoN CkluIG15IGNhc2UgaXQgc2hvdWxkIGJlIHNvbWV0aGluZyBsaWtlIHRoaXM6 IA0KQ1JFQVRFIFRBQkxFIHQxICgNCiAgYSBpbnRlZ2VyIFBSSU1BUlkgS0VZ LA0KICBiIGludGVnZXIsDQogIGMgaW50ZWdlciwNCiAgRk9SRUlHTiBLRVkg KGIpIFJFRkVSRU5DRVMgb3RoZXJfdGFibGUgKGMxLCBjMj0nYzJWYWx1ZScp DQopOw0KVGhpcyBleGFtcGxlIGRvZXMgbm90IHdvcmsuIEJ1dCBJIHRoaW5r IHlvdSBjYW4gc2VlLCB3aGF0IEkgbWVhbi4NCg0KDQoKLS0gClNlbnQgdmlh IHBnc3FsLXNxbCBtYWlsaW5nIGxpc3QgKHBnc3FsLXNxbEBwb3N0Z3Jlc3Fs Lm9yZykKVG8gbWFrZSBjaGFuZ2VzIHRvIHlvdXIgc3Vic2NyaXB0aW9uOgpo dHRwOi8vd3d3LnBvc3RncmVzcWwub3JnL21haWxwcmVmL3Bnc3FsLXNxbAo= From lucazeo@gmail.com Tue Sep 23 12:37:00 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWPL2-0002kV-Gd for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 12:37:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWPL1-0002Pf-SH for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 12:36:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWPL0-0002PY-Nl for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 12:36:58 +0000 Received: from mail-lb0-x233.google.com ([2a00:1450:4010:c04::233]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XWPKr-0002cg-AR for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 12:36:57 +0000 Received: by mail-lb0-f179.google.com with SMTP id 10so8142113lbg.10 for ; Tue, 23 Sep 2014 05:36:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; bh=q2otv4u0pvCb8vkIsm16PCsfSz0ZRCcIKfZgeUi7nng=; b=PNvd8sDwgRmwGaLmaXG3YB3DSyudiOd3BkFdZu6ZOmeU3LWyGdtD52T7jSdqixtsLQ vUTU5MImT7B+nqT617ZvV6fp2kKpHuN5JTPHidvHXWwgSw6Y+ZOgBVb/GaCuGJm9d7Np t8LfvHRIbv49qxnY7VM8cPGk1/RIq9FzY2jqzpM+Oe1/srducvaiVjKhdN75sivjCXgi 03Ud2795XZUB4AfuMF4svTWnO88Uhk2yOhUKWwsEiw3Nd0sisFXZsHS0p46kEtJ2vW6+ nnUzWqb6u8jv8ERfqJo+yMFg+l04snpzxqcu2EO6KHSZMtGbwfaQ1mRWnGGzEtdGIWB2 zk2w== MIME-Version: 1.0 X-Received: by 10.152.5.168 with SMTP id t8mr9943454lat.67.1411475807701; Tue, 23 Sep 2014 05:36:47 -0700 (PDT) Received: by 10.25.126.4 with HTTP; Tue, 23 Sep 2014 05:36:47 -0700 (PDT) In-Reply-To: <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> References: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> Date: Tue, 23 Sep 2014 14:36:47 +0200 Message-ID: Subject: Re: FOREIGN KEY Reference on multiple columns From: Luca Vernini To: =?UTF-8?B?V2Vpc3MsIErDtnJn?= Cc: "pgsql-sql@postgresql.org" Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org 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=3D'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=C3=B6rg : >> -----Urspr=C3=BCngliche Nachricht----- >> Von: Luca Vernini [mailto:lucazeo@gmail.com] >> Gesendet: Dienstag, 23. September 2014 11:59 >> An: Weiss, J=C3=B6rg >> Cc: pgsql-sql@postgresql.org >> Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns >> >> 2014-09-23 11:54 GMT+02:00 Weiss, J=C3=B6rg : >> > Hi! >> Hi. >> >> > Is it possible to create a =E2=80=9CFOREIGN KEY CONSTRAINT=E2=80=9D wi= th 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 =E2=80=A6 >> >> 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=3D'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 --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From J.Weiss@dvz-mv.de Tue Sep 23 13:10:55 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWPrr-0004Dz-2W for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 13:10:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWPrq-0005Uv-49 for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 13:10:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWPrm-0005NR-6k for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 13:10:50 +0000 Received: from vmailinpx1.mvnet.de ([194.25.109.14]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XWPrh-0003KF-MZ for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 13:10:48 +0000 Received: from vmgwpa1.mvnet.de (fw.mvnet.de [195.145.109.44]) by vmailinpx1.mvnet.de (/) with ESMTP id s8NDAhjg021331 for ; Tue, 23 Sep 2014 15:10:43 +0200 Received: from smtp.bk.dvz-mv.net ([10.254.1.150]) by vmgwpa1.mvnet.de (/) with ESMTP id s8NDAh9Y049287 for ; Tue, 23 Sep 2014 15:10:43 +0200 Received: from DVZSN-RA0325.bk.dvz-mv.net ([169.254.2.121]) by DVZSN-RA0323.bk.dvz-mv.net ([169.254.1.228]) with mapi id 14.02.0387.000; Tue, 23 Sep 2014 15:10:42 +0200 From: =?utf-8?B?V2Vpc3MsIErDtnJn?= To: "pgsql-sql@postgresql.org" Subject: Re: FOREIGN KEY Reference on multiple columns Thread-Topic: [SQL] FOREIGN KEY Reference on multiple columns Thread-Index: Ac/XEc5cK2GmPw+fQPCw4xE5O67V5v//5PUA///GunCAAGVBgP//2Jvg Date: Tue, 23 Sep 2014 13:10:42 +0000 Message-ID: <4B4E89127868BD458A795430BCF4FD1328C51AF4@DVZSN-RA0325.bk.dvz-mv.net> References: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> In-Reply-To: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.4.81.101] x-pmwin-version: 3.1.2.0, Antivirus-Engine: 3.51.1, Antivirus-Data: 5.06 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 MIME-Version: 1.0 X-Virus-Scanned: ClamAV using ClamSMTP X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org SSBtZWFuIGIgbXVzdCBlcXVhbCB0byBjMSAgaW4gdGhlICJvdGhlcl90YWJs ZSIgd2hlcmUgYzIgaGFzIGEgY2VydGFpbiB2YWx1ZSAoZm9yIGV4YW1wbGUg YzIgKS4gDQoNCkZvciBteSBmaXJzdCBleGFtcGxlOg0KQ1JFQVRFIFRBQkxF IHBhcm0NCigNCiAgY29tcGxleCB2YXJjaGFyKDIwKSBOT1QgTlVMTCwNCiAg cGFyYSB2YXJjaGFyKDUwKSBOT1QgTlVMTCwNCiAgc29ydCBpbnQ0IE5PVCBO VUxMIERFRkFVTFQgMTAsDQogIHZhbHVlIHZhcmNoYXIoNTApIE5VTEwsDQog Q09OU1RSQUlOVCBwYXJtX3BrZXkgUFJJTUFSWSBLRVkgKGNvbXBsZXgsIHBh cmEsIHNvcnQpDQopDQoNClRhYmxlIHVzZXIgDQpDUkVBVEUgVEFCTEUgdXNl cg0KKA0KICBuYW1lIHZhcmNoYXIoMjApIE5PVCBOVUxMLA0KICB0eXBlIGlu dGVnZXIgTlVMTA0KKQ0KSW4gdGhpcyBjYXNlICJ0eXBlIiBvZiB0YWJsZSB1 c2VyIG11c3QgZXF1YWwgdG8gInZhbHVlIiBvZiB0YWJsZSAicGFybSIgYW5k ICJwYXJhIiBtdXN0IGJlICJsb2dpbl91c2VyIiAoZm9yIGV4YW1wbGUpDQoN ClJlZ2FyZHMNCg0KSsO2cmcNCg0KPiAtLS0tLVVyc3Byw7xuZ2xpY2hlIE5h Y2hyaWNodC0tLS0tDQo+IFZvbjogTHVjYSBWZXJuaW5pIFttYWlsdG86bHVj YXplb0BnbWFpbC5jb21dDQo+IEdlc2VuZGV0OiBEaWVuc3RhZywgMjMuIFNl cHRlbWJlciAyMDE0IDE0OjM3DQo+IEFuOiBXZWlzcywgSsO2cmcNCj4gQ2M6 IHBnc3FsLXNxbEBwb3N0Z3Jlc3FsLm9yZw0KPiBCZXRyZWZmOiBSZTogW1NR TF0gRk9SRUlHTiBLRVkgUmVmZXJlbmNlIG9uIG11bHRpcGxlIGNvbHVtbnMN Cj4gDQo+IFNvcnJ5LCBJIGRvbid0IGdldCB5b3VyIHByb2JsZW0uDQo+IElu IGZpcnN0IGV4YW1wbGUgdGhlcmUgd2FzIGEgd2hlcmUsIGluIHNlY29uZCBj YXNlOg0KPiBGT1JFSUdOIEtFWSAoYikgUkVGRVJFTkNFUyBvdGhlcl90YWJs ZSAoYzEsIGMyPSdjMlZhbHVlJykNCj4gWW91IG1lYW4gYiBtdXN0IGJlIGVx dWFsIHRvIHdoYXQ/DQo+IFRyeSB0byBpbmNsdWRlIHJlYWwgdGFibGUgc2Ny aXB0Lg0KPiBQcm9iYWJseSB5b3UgY2FuIHVzZSBhIGNoZWNrLg0KPiANCj4g UmVnYXJkcywNCj4gDQo+IEx1Y2EuDQo+IA0KPiAyMDE0LTA5LTIzIDEzOjM4 IEdNVCswMjowMCBXZWlzcywgSsO2cmcgPEouV2Vpc3NAZHZ6LW12LmRlPjoN Cj4gPj4gLS0tLS1VcnNwcsO8bmdsaWNoZSBOYWNocmljaHQtLS0tLQ0KPiA+ PiBWb246IEx1Y2EgVmVybmluaSBbbWFpbHRvOmx1Y2F6ZW9AZ21haWwuY29t XQ0KPiA+PiBHZXNlbmRldDogRGllbnN0YWcsIDIzLiBTZXB0ZW1iZXIgMjAx NCAxMTo1OQ0KPiA+PiBBbjogV2Vpc3MsIErDtnJnDQo+ID4+IENjOiBwZ3Nx bC1zcWxAcG9zdGdyZXNxbC5vcmcNCj4gPj4gQmV0cmVmZjogUmU6IFtTUUxd IEZPUkVJR04gS0VZIFJlZmVyZW5jZSBvbiBtdWx0aXBsZSBjb2x1bW5zDQo+ ID4+DQo+ID4+IDIwMTQtMDktMjMgMTE6NTQgR01UKzAyOjAwIFdlaXNzLCBK w7ZyZyA8Si5XZWlzc0BkdnotbXYuZGU+Og0KPiA+PiA+IEhpIQ0KPiA+PiBI aS4NCj4gPj4NCj4gPj4gPiBJcyBpdCBwb3NzaWJsZSB0byBjcmVhdGUgYSDi gJxGT1JFSUdOIEtFWSBDT05TVFJBSU5U4oCdIHdpdGggcmVmZXJlbmNlcyB0 bw0KPiA+PiA+IG11bHRpcGxlIGNvbHVtbnMgb2YgdGhlIHJlZmVyZW5jZSB0 YWJsZT8NCj4gPj4NCj4gPj4gWWVzLCBpdCBpcy4NCj4gPj4NCj4gPj4gc2Vl IGhlcmU6DQo+ID4+IGh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvZG9jcy85 LjMvc3RhdGljL2RkbC1jb25zdHJhaW50cy5odG1sDQo+ID4+IFNlY3Rpb24g NS4zLjUgdGhlcmUgYXJlIHNvbWUgZXhhbXBsZXMuIE9uZSBvZiB0aGVtIGhh cyBtdWx0aXBsZQ0KPiA+PiBjb2x1bW5zLCBqdXN0IGxpa2UgeW91ciBjYXNl Lg0KPiA+Pg0KPiA+PiA+IFJlZ2FyZHMg4oCmDQo+ID4+DQo+ID4+IFJlZ2Fy ZHMuDQo+ID4NCj4gPiBUaGFuayB5b3UhDQo+ID4NCj4gPiBEbyB5b3UgbWVh biB0aGlzIGV4YW1wbGU/DQo+ID4gQ1JFQVRFIFRBQkxFIHQxICgNCj4gPiAg IGEgaW50ZWdlciBQUklNQVJZIEtFWSwNCj4gPiAgIGIgaW50ZWdlciwNCj4g PiAgIGMgaW50ZWdlciwNCj4gPiAgIEZPUkVJR04gS0VZIChiLCBjKSBSRUZF UkVOQ0VTIG90aGVyX3RhYmxlIChjMSwgYzIpDQo+ID4gKTsNCj4gPiBUaGlz IGlzIG5vdCwgd2hhdCBJIHdhbnQuDQo+ID4NCj4gPiBJbiBteSBjYXNlIGl0 IHNob3VsZCBiZSBzb21ldGhpbmcgbGlrZSB0aGlzOg0KPiA+IENSRUFURSBU QUJMRSB0MSAoDQo+ID4gICBhIGludGVnZXIgUFJJTUFSWSBLRVksDQo+ID4g ICBiIGludGVnZXIsDQo+ID4gICBjIGludGVnZXIsDQo+ID4gICBGT1JFSUdO IEtFWSAoYikgUkVGRVJFTkNFUyBvdGhlcl90YWJsZSAoYzEsIGMyPSdjMlZh bHVlJykNCj4gPiApOw0KPiA+IFRoaXMgZXhhbXBsZSBkb2VzIG5vdCB3b3Jr LiBCdXQgSSB0aGluayB5b3UgY2FuIHNlZSwgd2hhdCBJIG1lYW4uDQo+ID4N Cj4gPg0KPiA+DQo+ID4gLS0NCj4gPiBTZW50IHZpYSBwZ3NxbC1zcWwgbWFp bGluZyBsaXN0IChwZ3NxbC1zcWxAcG9zdGdyZXNxbC5vcmcpDQo+ID4gVG8g bWFrZSBjaGFuZ2VzIHRvIHlvdXIgc3Vic2NyaXB0aW9uOg0KPiA+IGh0dHA6 Ly93d3cucG9zdGdyZXNxbC5vcmcvbWFpbHByZWYvcGdzcWwtc3FsDQoKLS0g ClNlbnQgdmlhIHBnc3FsLXNxbCBtYWlsaW5nIGxpc3QgKHBnc3FsLXNxbEBw b3N0Z3Jlc3FsLm9yZykKVG8gbWFrZSBjaGFuZ2VzIHRvIHlvdXIgc3Vic2Ny aXB0aW9uOgpodHRwOi8vd3d3LnBvc3RncmVzcWwub3JnL21haWxwcmVmL3Bn c3FsLXNxbAo= From adrian.klaver@aklaver.com Tue Sep 23 13:15:29 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWPwH-0004Qz-8Y for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 13:15:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWPwG-0001VS-7z for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 13:15:28 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWPw9-0001I7-5V for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 13:15:21 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWPw1-0001Rq-9A for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 13:15:19 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by gateway2.nyi.internal (Postfix) with ESMTP id 7C24D20B23 for ; Tue, 23 Sep 2014 09:15:11 -0400 (EDT) Received: from frontend1 ([10.202.2.160]) by compute5.internal (MEProxy); Tue, 23 Sep 2014 09:15:11 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=aklaver.com; h= message-id:date:from:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding; s=mesmtp; bh=c4HkMrfx21tKt7dI78H3V4A0AmE=; b=k5IKOOEmAKiQcf6meOgPFA5kn3F7 SaaQ8HAtvI61JvztlYIjMwdPzJMqYGXGC9YdMkDMD9RSLzTcuT0YtdC8VNgUT2Bn HX1Y4uZeXcBZ4q5Bs+YIjwFi7ZmYecVAJLP8osFepXTQ4SVXmUafB2J9cZptn0Zd GH4X1dAmPHJfM5w= DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=message-id:date:from:mime-version:to :subject:references:in-reply-to:content-type :content-transfer-encoding; s=smtpout; bh=c4HkMrfx21tKt7dI78H3V4 A0AmE=; b=gcTlR/oGyv7ooHbEIS27RD8v7goqJvOJFJ0sjoNu/0MMRFSJTcTOR3 TDa2VISxUir48gL0WTlE+9dW3qsQKcE2SQwtA7ndWgfwwIB0uSZgyn9b8k1+HOHN QRe9UbEDRM5TI4ZGi27iX4+ciC/ITNM54KAidWclUKvhp/h+3pX+g= X-Sasl-enc: J1htZfuGg8Eo0cn84Wv0DGCx6++DvJCzSOePGqepyqhq 1411478111 Received: from [192.168.1.5] (unknown [174.21.177.1]) by mail.messagingengine.com (Postfix) with ESMTPA id 0EC38C00915; Tue, 23 Sep 2014 09:15:10 -0400 (EDT) Message-ID: <5421725E.3030007@aklaver.com> Date: Tue, 23 Sep 2014 06:15:10 -0700 From: Adrian Klaver User-Agent: Mozilla/5.0 (X11; Linux i686; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 To: =?UTF-8?B?IldlaXNzLCBKw7ZyZyI=?= , "pgsql-sql@postgresql.org" Subject: Re: FOREIGN KEY Reference on multiple columns References: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> In-Reply-To: <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org 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 : >>> 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 From lucazeo@gmail.com Tue Sep 23 13:17:55 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWPyd-0004Vi-9f for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 13:17:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWPyc-0003Qz-H2 for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 13:17:54 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWPyb-0003Qs-Nn for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 13:17:53 +0000 Received: from mail-lb0-x22c.google.com ([2a00:1450:4010:c04::22c]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XWPyX-0001WK-Sb for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 13:17:51 +0000 Received: by mail-lb0-f172.google.com with SMTP id p9so8097749lbv.31 for ; Tue, 23 Sep 2014 06:17:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; bh=nNrGvZSSPXtDebpugu62r27TbCfgP1QvzEqEaS8JlTA=; b=kqoWisdcj12yPEGYwsud3P7o94mGS6emAlzw1IsWeWsjPNO+T6GzHBWFGbfezFkzJZ zQARE3Ru78Dr6kRJK3XGVDeV6S1nV46wsONAztujoKSurfVujEIW8LJXqe2j0BwZmjj6 pMVtQ7YOWdJE0sQAE4lT8zKNn0T6aMJKRPwjHCItni9OQWAdj7iVoIbb+Zqp2cJm8Vzj baw6cHn0vMAqoQUhXvyBMdYLhNQRfCeuPgxEfXfeJUcWeB6CfLrHWlqV/q4RidfxaKyN RRbwwXW6USyoz1aOXc7CFWCFFm6pJ7aulhMyLq5i1bWGspDttwhDAo4NiiSUQQFoBnHd bmeA== MIME-Version: 1.0 X-Received: by 10.152.234.76 with SMTP id uc12mr33241557lac.50.1411478267511; Tue, 23 Sep 2014 06:17:47 -0700 (PDT) Received: by 10.25.126.4 with HTTP; Tue, 23 Sep 2014 06:17:47 -0700 (PDT) In-Reply-To: <4B4E89127868BD458A795430BCF4FD1328C51AF4@DVZSN-RA0325.bk.dvz-mv.net> References: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51AF4@DVZSN-RA0325.bk.dvz-mv.net> Date: Tue, 23 Sep 2014 15:17:47 +0200 Message-ID: Subject: Re: FOREIGN KEY Reference on multiple columns From: Luca Vernini To: =?UTF-8?B?V2Vpc3MsIErDtnJn?= Cc: "pgsql-sql@postgresql.org" Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org 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=C3=B6rg : > I mean b must equal to c1 in the "other_table" where c2 has a certain va= lue (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" a= nd "para" must be "login_user" (for example) > > Regards > > J=C3=B6rg > >> -----Urspr=C3=BCngliche Nachricht----- >> Von: Luca Vernini [mailto:lucazeo@gmail.com] >> Gesendet: Dienstag, 23. September 2014 14:37 >> An: Weiss, J=C3=B6rg >> 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=3D'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=C3=B6rg : >> >> -----Urspr=C3=BCngliche Nachricht----- >> >> Von: Luca Vernini [mailto:lucazeo@gmail.com] >> >> Gesendet: Dienstag, 23. September 2014 11:59 >> >> An: Weiss, J=C3=B6rg >> >> Cc: pgsql-sql@postgresql.org >> >> Betreff: Re: [SQL] FOREIGN KEY Reference on multiple columns >> >> >> >> 2014-09-23 11:54 GMT+02:00 Weiss, J=C3=B6rg : >> >> > Hi! >> >> Hi. >> >> >> >> > Is it possible to create a =E2=80=9CFOREIGN KEY CONSTRAINT=E2=80=9D= 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 =E2=80=A6 >> >> >> >> 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=3D'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 --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From J.Weiss@dvz-mv.de Tue Sep 23 13:38:41 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWQIj-0005Op-ER for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 13:38:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWQIi-0005mF-PI for pgsql-sql@arkaria.postgresql.org; Tue, 23 Sep 2014 13:38:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWQIh-0005jz-0F for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 13:38:39 +0000 Received: from vmailinpx1.mvnet.de ([194.25.109.14]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XWQIc-0003u9-PK for pgsql-sql@postgresql.org; Tue, 23 Sep 2014 13:38:37 +0000 Received: from vmgwpa1.mvnet.de (fw.mvnet.de [195.145.109.44]) by vmailinpx1.mvnet.de (/) with ESMTP id s8NDcY9K024607 for ; Tue, 23 Sep 2014 15:38:34 +0200 Received: from smtp.bk.dvz-mv.net ([10.254.1.150]) by vmgwpa1.mvnet.de (/) with ESMTP id s8NDcXe5059578 for ; Tue, 23 Sep 2014 15:38:33 +0200 Received: from DVZSN-RA0325.bk.dvz-mv.net ([169.254.2.121]) by DVZSN-RA0323.bk.dvz-mv.net ([169.254.1.228]) with mapi id 14.02.0387.000; Tue, 23 Sep 2014 15:38:33 +0200 From: =?utf-8?B?V2Vpc3MsIErDtnJn?= To: "pgsql-sql@postgresql.org" Subject: Re: FOREIGN KEY Reference on multiple columns Thread-Topic: [SQL] FOREIGN KEY Reference on multiple columns Thread-Index: Ac/XEc5cK2GmPw+fQPCw4xE5O67V5v//5PUA///GunCAAGVBgP//2JvggAAy2YD//91T0A== Date: Tue, 23 Sep 2014 13:38:32 +0000 Message-ID: <4B4E89127868BD458A795430BCF4FD1328C51B1B@DVZSN-RA0325.bk.dvz-mv.net> References: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51AF4@DVZSN-RA0325.bk.dvz-mv.net> In-Reply-To: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.4.81.101] x-pmwin-version: 3.1.2.0, Antivirus-Engine: 3.51.1, Antivirus-Data: 5.06 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: base64 MIME-Version: 1.0 X-Virus-Scanned: ClamAV using ClamSMTP X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org VGhhdCdzIHdoYXQgSSB3YW50ZWQgdG8ga25vdy4NCkl0J3Mgbm90IG9ubHkg YSBjb25zdHJhaW50IHRvIGEgImNlcnRhaW4gdmFsdWUiIG9yIChiZXR0ZXIp ICJzcGVjaWZpYyB2YWx1ZSIsIGl0IHNob3VsZCBiZSBhIGNvbnN0cmFpbnQg dG8gYSBjb21iaW5hdGlvbiBvZiB0aGUgdmFsdWVzIG9mIDIgY29sdW1ucy4g DQoidHlwZSIgb2YgdGFibGUgInVzZXIiIG11c3QgZXF1YWwgdG8gInZhbHVl IiBvZiB0YWJsZSAicGFybSIgd2hlcmUgInBhcmEiIG9mIHRhYmxlICJwYXJt IiAgbXVzdCBhbHdheXMgYmUgImxvZ2luX3VzZXIiLg0KQ2hlY2sgb3IgYSBm dW5jdGlvbiBpcyBub3QgdGhlIHNhbWUuIEEgImZvcmVpZ24ga2V5IiB3b3Jr cyBvbiBib3RoIHNpZGVzLiBUYWJsZSAidXNlciIgYW5kIHRhYmxlICJwYXJt Ii4NCk9LIEkgY2FuIGRvIGNoZWNrcyBhdCBib3RoIHRhYmxlcyBPTiBVUERB VEUsIElOU0VSVCBvciBERUxFVEUuIFRoaXMgbWF5IHdvcmsuICANCg0KUmVn YXJkcw0KDQpKw7ZyZw0KDQo+IC0tLS0tVXJzcHLDvG5nbGljaGUgTmFjaHJp Y2h0LS0tLS0NCj4gVm9uOiBMdWNhIFZlcm5pbmkgW21haWx0bzpsdWNhemVv QGdtYWlsLmNvbV0NCj4gR2VzZW5kZXQ6IERpZW5zdGFnLCAyMy4gU2VwdGVt YmVyIDIwMTQgMTU6MTgNCj4gQW46IFdlaXNzLCBKw7ZyZw0KPiBDYzogcGdz cWwtc3FsQHBvc3RncmVzcWwub3JnDQo+IEJldHJlZmY6IFJlOiBbU1FMXSBG T1JFSUdOIEtFWSBSZWZlcmVuY2Ugb24gbXVsdGlwbGUgY29sdW1ucw0KPiAN Cj4gSSBzZWUuDQo+IFlvdSBjYW5ub3QgZG8gYSBjb25zdHJhaW50IHdpdGgg YSAiY2VydGFpbiB2YWx1ZSIsIGlmIEkgdW5kZXJzdG9vZA0KPiByaWdodC4g VGhpcyBpcyBub3QgZXZlbiBwb3NzaWJsZSBpbiBhbnkga25vdyByZGJtcywg YmVjYXVzZSBhDQo+IGNvbnN0cmFpbnQgbXVzdCByZWZlcmVuY2Ugc29tZXRo aW5nIHN0b3JlZCBvbiB0YWJsZXMuIFBlcnNpc3RlZCBvbiBkYi4NCj4gWW91 IGNhbiBvbmx5IGNoZWNrIHZhbHVlcyB3aGlsZSBpbnNlcnRpbmcuIEkgc3Vn Z2VzdCB1c2VpbmcgYSBmdW5jdGlvbi4NCj4gDQo+IFJlZ2FyZHMsDQo+IA0K PiBMdWNhLg0KPiANCj4gMjAxNC0wOS0yMyAxNToxMCBHTVQrMDI6MDAgV2Vp c3MsIErDtnJnIDxKLldlaXNzQGR2ei1tdi5kZT46DQo+ID4gSSBtZWFuIGIg bXVzdCBlcXVhbCB0byBjMSAgaW4gdGhlICJvdGhlcl90YWJsZSIgd2hlcmUg YzIgaGFzIGEgY2VydGFpbiB2YWx1ZQ0KPiAoZm9yIGV4YW1wbGUgYzIgKS4N Cj4gPg0KPiA+IEZvciBteSBmaXJzdCBleGFtcGxlOg0KPiA+IENSRUFURSBU QUJMRSBwYXJtDQo+ID4gKA0KPiA+ICAgY29tcGxleCB2YXJjaGFyKDIwKSBO T1QgTlVMTCwNCj4gPiAgIHBhcmEgdmFyY2hhcig1MCkgTk9UIE5VTEwsDQo+ ID4gICBzb3J0IGludDQgTk9UIE5VTEwgREVGQVVMVCAxMCwNCj4gPiAgIHZh bHVlIHZhcmNoYXIoNTApIE5VTEwsDQo+ID4gIENPTlNUUkFJTlQgcGFybV9w a2V5IFBSSU1BUlkgS0VZIChjb21wbGV4LCBwYXJhLCBzb3J0KQ0KPiA+ICkN Cj4gPg0KPiA+IFRhYmxlIHVzZXINCj4gPiBDUkVBVEUgVEFCTEUgdXNlcg0K PiA+ICgNCj4gPiAgIG5hbWUgdmFyY2hhcigyMCkgTk9UIE5VTEwsDQo+ID4g ICB0eXBlIGludGVnZXIgTlVMTA0KPiA+ICkNCj4gPiBJbiB0aGlzIGNhc2Ug InR5cGUiIG9mIHRhYmxlIHVzZXIgbXVzdCBlcXVhbCB0byAidmFsdWUiIG9m IHRhYmxlICJwYXJtIiBhbmQNCj4gInBhcmEiIG11c3QgYmUgImxvZ2luX3Vz ZXIiIChmb3IgZXhhbXBsZSkNCj4gPg0KPiA+IFJlZ2FyZHMNCj4gPg0KPiA+ IErDtnJnDQo+ID4NCj4gPj4gLS0tLS1VcnNwcsO8bmdsaWNoZSBOYWNocmlj aHQtLS0tLQ0KPiA+PiBWb246IEx1Y2EgVmVybmluaSBbbWFpbHRvOmx1Y2F6 ZW9AZ21haWwuY29tXQ0KPiA+PiBHZXNlbmRldDogRGllbnN0YWcsIDIzLiBT ZXB0ZW1iZXIgMjAxNCAxNDozNw0KPiA+PiBBbjogV2Vpc3MsIErDtnJnDQo+ ID4+IENjOiBwZ3NxbC1zcWxAcG9zdGdyZXNxbC5vcmcNCj4gPj4gQmV0cmVm ZjogUmU6IFtTUUxdIEZPUkVJR04gS0VZIFJlZmVyZW5jZSBvbiBtdWx0aXBs ZSBjb2x1bW5zDQo+ID4+DQo+ID4+IFNvcnJ5LCBJIGRvbid0IGdldCB5b3Vy IHByb2JsZW0uDQo+ID4+IEluIGZpcnN0IGV4YW1wbGUgdGhlcmUgd2FzIGEg d2hlcmUsIGluIHNlY29uZCBjYXNlOg0KPiA+PiBGT1JFSUdOIEtFWSAoYikg UkVGRVJFTkNFUyBvdGhlcl90YWJsZSAoYzEsIGMyPSdjMlZhbHVlJykNCj4g Pj4gWW91IG1lYW4gYiBtdXN0IGJlIGVxdWFsIHRvIHdoYXQ/DQo+ID4+IFRy eSB0byBpbmNsdWRlIHJlYWwgdGFibGUgc2NyaXB0Lg0KPiA+PiBQcm9iYWJs eSB5b3UgY2FuIHVzZSBhIGNoZWNrLg0KPiA+Pg0KPiA+PiBSZWdhcmRzLA0K PiA+Pg0KPiA+PiBMdWNhLg0KPiA+Pg0KPiA+PiAyMDE0LTA5LTIzIDEzOjM4 IEdNVCswMjowMCBXZWlzcywgSsO2cmcgPEouV2Vpc3NAZHZ6LW12LmRlPjoN Cj4gPj4gPj4gLS0tLS1VcnNwcsO8bmdsaWNoZSBOYWNocmljaHQtLS0tLQ0K PiA+PiA+PiBWb246IEx1Y2EgVmVybmluaSBbbWFpbHRvOmx1Y2F6ZW9AZ21h aWwuY29tXQ0KPiA+PiA+PiBHZXNlbmRldDogRGllbnN0YWcsIDIzLiBTZXB0 ZW1iZXIgMjAxNCAxMTo1OQ0KPiA+PiA+PiBBbjogV2Vpc3MsIErDtnJnDQo+ ID4+ID4+IENjOiBwZ3NxbC1zcWxAcG9zdGdyZXNxbC5vcmcNCj4gPj4gPj4g QmV0cmVmZjogUmU6IFtTUUxdIEZPUkVJR04gS0VZIFJlZmVyZW5jZSBvbiBt dWx0aXBsZSBjb2x1bW5zDQo+ID4+ID4+DQo+ID4+ID4+IDIwMTQtMDktMjMg MTE6NTQgR01UKzAyOjAwIFdlaXNzLCBKw7ZyZyA8Si5XZWlzc0BkdnotbXYu ZGU+Og0KPiA+PiA+PiA+IEhpIQ0KPiA+PiA+PiBIaS4NCj4gPj4gPj4NCj4g Pj4gPj4gPiBJcyBpdCBwb3NzaWJsZSB0byBjcmVhdGUgYSDigJxGT1JFSUdO IEtFWSBDT05TVFJBSU5U4oCdIHdpdGggcmVmZXJlbmNlcw0KPiB0bw0KPiA+ PiA+PiA+IG11bHRpcGxlIGNvbHVtbnMgb2YgdGhlIHJlZmVyZW5jZSB0YWJs ZT8NCj4gPj4gPj4NCj4gPj4gPj4gWWVzLCBpdCBpcy4NCj4gPj4gPj4NCj4g Pj4gPj4gc2VlIGhlcmU6DQo+ID4+ID4+IGh0dHA6Ly93d3cucG9zdGdyZXNx bC5vcmcvZG9jcy85LjMvc3RhdGljL2RkbC1jb25zdHJhaW50cy5odG1sDQo+ ID4+ID4+IFNlY3Rpb24gNS4zLjUgdGhlcmUgYXJlIHNvbWUgZXhhbXBsZXMu IE9uZSBvZiB0aGVtIGhhcyBtdWx0aXBsZQ0KPiA+PiA+PiBjb2x1bW5zLCBq dXN0IGxpa2UgeW91ciBjYXNlLg0KPiA+PiA+Pg0KPiA+PiA+PiA+IFJlZ2Fy ZHMg4oCmDQo+ID4+ID4+DQo+ID4+ID4+IFJlZ2FyZHMuDQo+ID4+ID4NCj4g Pj4gPiBUaGFuayB5b3UhDQo+ID4+ID4NCj4gPj4gPiBEbyB5b3UgbWVhbiB0 aGlzIGV4YW1wbGU/DQo+ID4+ID4gQ1JFQVRFIFRBQkxFIHQxICgNCj4gPj4g PiAgIGEgaW50ZWdlciBQUklNQVJZIEtFWSwNCj4gPj4gPiAgIGIgaW50ZWdl ciwNCj4gPj4gPiAgIGMgaW50ZWdlciwNCj4gPj4gPiAgIEZPUkVJR04gS0VZ IChiLCBjKSBSRUZFUkVOQ0VTIG90aGVyX3RhYmxlIChjMSwgYzIpDQo+ID4+ ID4gKTsNCj4gPj4gPiBUaGlzIGlzIG5vdCwgd2hhdCBJIHdhbnQuDQo+ID4+ ID4NCj4gPj4gPiBJbiBteSBjYXNlIGl0IHNob3VsZCBiZSBzb21ldGhpbmcg bGlrZSB0aGlzOg0KPiA+PiA+IENSRUFURSBUQUJMRSB0MSAoDQo+ID4+ID4g ICBhIGludGVnZXIgUFJJTUFSWSBLRVksDQo+ID4+ID4gICBiIGludGVnZXIs DQo+ID4+ID4gICBjIGludGVnZXIsDQo+ID4+ID4gICBGT1JFSUdOIEtFWSAo YikgUkVGRVJFTkNFUyBvdGhlcl90YWJsZSAoYzEsIGMyPSdjMlZhbHVlJykN Cj4gPj4gPiApOw0KPiA+PiA+IFRoaXMgZXhhbXBsZSBkb2VzIG5vdCB3b3Jr LiBCdXQgSSB0aGluayB5b3UgY2FuIHNlZSwgd2hhdCBJIG1lYW4uDQo+ID4+ ID4NCj4gPj4gPg0KPiA+PiA+DQo+ID4+ID4gLS0NCj4gPj4gPiBTZW50IHZp YSBwZ3NxbC1zcWwgbWFpbGluZyBsaXN0IChwZ3NxbC1zcWxAcG9zdGdyZXNx bC5vcmcpDQo+ID4+ID4gVG8gbWFrZSBjaGFuZ2VzIHRvIHlvdXIgc3Vic2Ny aXB0aW9uOg0KPiA+PiA+IGh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvbWFp bHByZWYvcGdzcWwtc3FsDQo+ID4NCj4gPiAtLQ0KPiA+IFNlbnQgdmlhIHBn c3FsLXNxbCBtYWlsaW5nIGxpc3QgKHBnc3FsLXNxbEBwb3N0Z3Jlc3FsLm9y ZykNCj4gPiBUbyBtYWtlIGNoYW5nZXMgdG8geW91ciBzdWJzY3JpcHRpb246 DQo+ID4gaHR0cDovL3d3dy5wb3N0Z3Jlc3FsLm9yZy9tYWlscHJlZi9wZ3Nx bC1zcWwNCgotLSAKU2VudCB2aWEgcGdzcWwtc3FsIG1haWxpbmcgbGlzdCAo cGdzcWwtc3FsQHBvc3RncmVzcWwub3JnKQpUbyBtYWtlIGNoYW5nZXMgdG8g eW91ciBzdWJzY3JpcHRpb246Cmh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcv bWFpbHByZWYvcGdzcWwtc3FsCg== From tim@tim-landscheidt.de Wed Sep 24 18:02:58 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XWqu2-000430-Rg for pgsql-sql@arkaria.postgresql.org; Wed, 24 Sep 2014 18:02:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XWqu2-0004L3-AD for pgsql-sql@arkaria.postgresql.org; Wed, 24 Sep 2014 18:02:58 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XWqu0-0004IJ-W3 for pgsql-sql@postgresql.org; Wed, 24 Sep 2014 18:02:57 +0000 Received: from plane.gmane.org ([80.91.229.3]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XWqtt-00005W-22 for pgsql-sql@postgresql.org; Wed, 24 Sep 2014 18:02:55 +0000 Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1XWqtq-0006RD-Fn for pgsql-sql@postgresql.org; Wed, 24 Sep 2014 20:02:46 +0200 Received: from e177160207.adsl.alicedsl.de ([85.177.160.207]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 24 Sep 2014 20:02:46 +0200 Received: from tim by e177160207.adsl.alicedsl.de with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 24 Sep 2014 20:02:46 +0200 X-Injected-Via-Gmane: http://gmane.org/ Mail-Followup-To: pgsql-sql@postgresql.org To: pgsql-sql@postgresql.org From: Tim Landscheidt Subject: Re: FOREIGN KEY Reference on multiple columns Date: Wed, 24 Sep 2014 18:02:34 +0000 Organization: http://www.tim-landscheidt.de/ Lines: 31 Message-ID: <87y4t8rj5x.fsf@passepartout.tim-landscheidt.de> References: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51AF4@DVZSN-RA0325.bk.dvz-mv.net> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: e177160207.adsl.alicedsl.de Mail-Copies-To: never User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux) Cancel-Lock: sha1:GRvu2LAyB6X+2cH/HgYSeI32iYE= X-Pg-Spam-Score: -3.3 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org "Weiss, Jrg" 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 From J.Weiss@dvz-mv.de Thu Sep 25 05:04:49 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XX1EW-0002nG-KG for pgsql-sql@arkaria.postgresql.org; Thu, 25 Sep 2014 05:04:49 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XX1EV-0002bX-VM for pgsql-sql@arkaria.postgresql.org; Thu, 25 Sep 2014 05:04:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XX1EU-0002bQ-Ts for pgsql-sql@postgresql.org; Thu, 25 Sep 2014 05:04:46 +0000 Received: from vmailinpx1.mvnet.de ([194.25.109.14]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XX1ER-0006jX-Ib for pgsql-sql@postgresql.org; Thu, 25 Sep 2014 05:04:45 +0000 Received: from vmgwpa1.mvnet.de (fw.mvnet.de [195.145.109.44]) by vmailinpx1.mvnet.de (/) with ESMTP id s8P54fbP018600 for ; Thu, 25 Sep 2014 07:04:41 +0200 Received: from smtp.bk.dvz-mv.net (dvzsn-rs2151.intranet.cn-mv.de [10.254.1.151]) by vmgwpa1.mvnet.de (/) with ESMTP id s8P54ftk055942 for ; Thu, 25 Sep 2014 07:04:41 +0200 Received: from DVZSN-RA0325.bk.dvz-mv.net ([169.254.2.121]) by DVZSN-RA0323.bk.dvz-mv.net ([169.254.1.228]) with mapi id 14.02.0387.000; Thu, 25 Sep 2014 07:04:41 +0200 From: =?iso-8859-1?Q?Weiss=2C_J=F6rg?= To: "pgsql-sql@postgresql.org" Subject: Re: FOREIGN KEY Reference on multiple columns Thread-Topic: [SQL] FOREIGN KEY Reference on multiple columns Thread-Index: AQHP2CG3K2GmPw+fQPCw4xE5O67V5pwRSz8Q Date: Thu, 25 Sep 2014 05:04:40 +0000 Message-ID: <4B4E89127868BD458A795430BCF4FD1328C53C1B@DVZSN-RA0325.bk.dvz-mv.net> References: <4B4E89127868BD458A795430BCF4FD1328C51A6F@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51ACB@DVZSN-RA0325.bk.dvz-mv.net> <4B4E89127868BD458A795430BCF4FD1328C51AF4@DVZSN-RA0325.bk.dvz-mv.net> <87y4t8rj5x.fsf@passepartout.tim-landscheidt.de> In-Reply-To: <87y4t8rj5x.fsf@passepartout.tim-landscheidt.de> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.4.81.101] x-pmwin-version: 3.1.2.0, Antivirus-Engine: 3.51.1, Antivirus-Data: 5.06 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Virus-Scanned: ClamAV using ClamSMTP X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org > -----Urspr=FCngliche 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 >=20 > "Weiss, J=F6rg" wrote: >=20 > > I mean b must equal to c1 in the "other_table" where c2 has a certain = value > (for example c2 ). >=20 > > 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) > > ) >=20 > > 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) >=20 > > [...] >=20 > 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. >=20 > Tim >=20 Yes of cause, this seems to be the best way.=20 Thank You! --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql