Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BE4A56347C5 for ; Mon, 14 Sep 2009 11:18:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 87640-08 for ; Mon, 14 Sep 2009 14:18:29 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 86F69633EE3 for ; Mon, 14 Sep 2009 11:18:29 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n8EEIF6U015922; Mon, 14 Sep 2009 10:18:15 -0400 (EDT) To: Mario Splivalo cc: pgsql-sql@postgresql.org Subject: Re: CHECK constraint on multiple tables In-reply-to: <4AAE43F8.4000107@megafon.hr> References: <4AAE43F8.4000107@megafon.hr> Comments: In-reply-to Mario Splivalo message dated "Mon, 14 Sep 2009 15:24:08 +0200" Date: Mon, 14 Sep 2009 10:18:15 -0400 Message-ID: <15921.1252937895@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200909/59 X-Sequence-Number: 33124 Mario Splivalo writes: > I have two tables, tableA and tableB: > CREATE TABLE tableA (idA integer primary key, email character varying > unique); > CREATE TABLE tableB (idB integer primary key, email character varying > unique); > Now, I want to create check constraint in both tables that would > disallow records to either table where email is 'mentioned' in other table. Have you considered refactoring so there's only one table? Cross-table constraints are a really bad idea unless you can express them as foreign keys. There's a lot of "secret sauce" in the FK mechanism that isn't available to user-written constraints. regards, tom lane