Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D8E1963490E for ; Mon, 14 Sep 2009 10:58:40 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 08354-02-3 for ; Mon, 14 Sep 2009 13:58:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from megafon.hr (megafon.hr [213.202.104.5]) by mail.postgresql.org (Postfix) with ESMTP id 12B36634F53 for ; Mon, 14 Sep 2009 10:58:22 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by megafon.hr (Postfix) with ESMTP id 0951C2500FB; Mon, 14 Sep 2009 15:58:19 +0200 (CEST) X-Virus-Scanned: ClamAV at megafon.hr Received: from megafon.hr ([127.0.0.1]) by localhost (localhost.localdomain [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 7ZagGd6aKxtS; Mon, 14 Sep 2009 15:58:12 +0200 (CEST) Received: from [192.168.1.94] (localhost.localdomain [127.0.0.1]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: mike) by megafon.hr (Postfix) with ESMTPSA id E0B852500DB; Mon, 14 Sep 2009 15:58:12 +0200 (CEST) Message-ID: <4AAE4BF4.1040100@megafon.hr> Date: Mon, 14 Sep 2009 15:58:12 +0200 From: Mario Splivalo User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: Ries van Twisk CC: pgsql-sql@postgresql.org Subject: Re: CHECK constraint on multiple tables References: <4AAE43F8.4000107@megafon.hr> <24123985-9812-49FE-B422-31E7AC963758@rvt.dds.nl> In-Reply-To: <24123985-9812-49FE-B422-31E7AC963758@rvt.dds.nl> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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/58 X-Sequence-Number: 33123 How would you do it, without creating third table? Mario Ries van Twisk wrote: > can't you solve it creating a reference between the tables? > > Ries > On Sep 14, 2009, at 8:24 AM, Mario Splivalo wrote: > >> 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. >> >> If CHECK constraints supported SUBSELECTS, I could write: >> >> ALTER TABLE tableA ADD CONSTRAINT tableA_chk CHECK >> (email NOT IN (SELECT email FROM tableB)); >> >> Unfortunatley, postgres won't allow me to do so. >> >> Now, i could create function, check_for_email, that would return TRUE if >> email is mentioned in either table, and then call that function when >> creating a check constraint. >> >> Or I could add separate table, emails, like this: >> >> CREATE TABLE emails (email_id integer primary key, email character >> varying unique) >> >> And then replace 'email' column in tables tableA and tableB with >> 'email_id' that would be foreign key refference to the emails table. >> >> I could, also, write functions for inserting data to the tableA and >> tableB tables. >> >> What would be the best approach to solve the problem I have? Could I use >> rules on insert to help me? >> >> Mario >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > regards, Ries van Twisk > > ------------------------------------------------------------------------------------------------- > > tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS > WebORB PostgreSQL DB-Architect > email: ries@vantwisk.nl web: http://www.rvantwisk.nl/ skype: > callto://r.vantwisk > Phone: +1-810-476-4196 Cell: +593 9901 7694 SIP: > +1-747-690-5133 > > > > > > > >