CHECK constraint on multiple tables

From: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>
To: pgsql-sql(at)postgresql(dot)org
Subject: CHECK constraint on multiple tables
Date: 2009-09-14 13:24:08
Message-ID: 4AAE43F8.4000107@megafon.hr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ries van Twisk 2009-09-14 13:27:53 Re: CHECK constraint on multiple tables
Previous Message Adrian Klaver 2009-09-13 18:37:59 Re: Serious problems with non-primary foreign keys