Обсуждение: CHECK constraint on multiple tables
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
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: ries@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
How would you do it, without creating third table? Mario Ries van Twisk wrote: > 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: ries@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 > > > > > > > >
Mario Splivalo <mario.splivalo@megafon.hr> writes:
> 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.
Have you considered refactoring so there's only one table?
Cross-table constraints are a really bad idea unless you can express
them as foreign keys. There's a lot of "secret sauce" in the FK
mechanism that isn't available to user-written constraints.
regards, tom lane
Tom Lane wrote: > Mario Splivalo <mario.splivalo@megafon.hr> writes: >> 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. > > Have you considered refactoring so there's only one table? Unfortunately I can't do that, due to the object-relational-mapper-wrapper-mambo-jumbo. The only 'logical' idea that I can think of is separating emails to the third table, and then use UNIQUE constraint on the email field on that table, and then use FK constraint so that email fields in tables tableA and tableB points to the email in the table emails. Mario
Mario Splivalo wrote on 14.09.2009 16:20: >> Have you considered refactoring so there's only one table? > > Unfortunately I can't do that, due to the > object-relational-mapper-wrapper-mambo-jumbo. > You could still refactor that into one single table, then create two updateable views with the names that the dreaded ORmapper expects. That would enable you to have a proper unique check on the base table, and you OR mapper still sees twotables that it can update. Thomas
Mario Splivalo wrote: > Tom Lane wrote: > >> Mario Splivalo <mario.splivalo@megafon.hr> writes: >> >>> 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. >>> >> Have you considered refactoring so there's only one table? >> > > Unfortunately I can't do that, due to the > object-relational-mapper-wrapper-mambo-jumbo. > > The only 'logical' idea that I can think of is separating emails to the > third table, and then use UNIQUE constraint on the email field on that > table, and then use FK constraint so that email fields in tables tableA > and tableB points to the email in the table emails. > > Mario > > Is that wrapper hibernate by any chance? If so you might try adding a discriminator column to the single table TL suggested and map each class accordingly.