Message-ID: <4AB05CB8.1040306@gmail.com>
Date: Tue, 15 Sep 2009 21:34:16 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Mario Splivalo <mario.splivalo@megafon.hr>
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-sql@postgresql.org
References: <4AAE43F8.4000107@megafon.hr> <15921.1252937895@sss.pgh.pa.us>
	<4AAE5141.70300@megafon.hr>
In-Reply-To: <4AAE5141.70300@megafon.hr>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Subject: Re: CHECK constraint on multiple tables

Mario Splivalo wrote:
> Tom Lane wrote:
>   
>> Mario Splivalo <mario.splivalo@megafon.hr> 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?
>>     
>
> Unfortunately I can't do that, due to the
> object-relational-mapper-wrapper-mambo-jumbo.
>
> The only 'logical' idea that I can think of is separating emails to the
> third table, and then use UNIQUE constraint on the email field on that
> table, and then use FK constraint so that email fields in tables tableA
> and tableB points to the email in the table emails.
>
> 	Mario
>
>   
Is that wrapper hibernate by any chance?  If so you might try adding a 
discriminator column to the single table TL suggested and map each class 
accordingly.