From: Mario Splivalo <mario.splivalo@megafon.hr>
To: Ries van Twisk <pg@rvt.dds.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: CHECK constraint on multiple tables
Date: Mon, 14 Sep 2009 15:58:12 +0200
Message-ID: <4AAE4BF4.1040100@megafon.hr> (raw)
In-Reply-To: <24123985-9812-49FE-B422-31E7AC963758@rvt.dds.nl>
References: <4AAE43F8.4000107@megafon.hr>
<24123985-9812-49FE-B422-31E7AC963758@rvt.dds.nl>
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
>
>
>
>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mario.splivalo@megafon.hr, pg@rvt.dds.nl
Subject: Re: CHECK constraint on multiple tables
In-Reply-To: <4AAE4BF4.1040100@megafon.hr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox