From: | Ries van Twisk <pg(at)rvt(dot)dds(dot)nl> |
---|---|
To: | Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: CHECK constraint on multiple tables |
Date: | 2009-09-14 13:27:53 |
Message-ID: | 24123985-9812-49FE-B422-31E7AC963758@rvt.dds.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)postgresql(dot)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(at)vantwisk(dot)nl web: http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: +1-810-476-4196 Cell: +593 9901 7694 SIP:
+1-747-690-5133
From | Date | Subject | |
---|---|---|---|
Next Message | Mario Splivalo | 2009-09-14 13:58:12 | Re: CHECK constraint on multiple tables |
Previous Message | Mario Splivalo | 2009-09-14 13:24:08 | CHECK constraint on multiple tables |