From: Mario Splivalo <mario.splivalo@megafon.hr>
To: pgsql-sql@postgresql.org
Subject: CHECK constraint on multiple tables
Date: Mon, 14 Sep 2009 15:24:08 +0200
Message-ID: <4AAE43F8.4000107@megafon.hr> (raw)
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
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
Subject: Re: CHECK constraint on multiple tables
In-Reply-To: <4AAE43F8.4000107@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