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: r...@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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
