CHECK constraint on multiple tables
От | Mario Splivalo |
---|---|
Тема | CHECK constraint on multiple tables |
Дата | |
Msg-id | 4AAE43F8.4000107@megafon.hr обсуждение исходный текст |
Ответы |
Re: CHECK constraint on multiple tables
Re: CHECK constraint on multiple tables |
Список | 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
В списке pgsql-sql по дате отправления: