[SQL] 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint on multiple tables
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 ([email protected]) 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: [email protected]: http://www.rvantwisk.nl/ skype: callto://r.vantwisk Phone: +1-810-476-4196Cell: +593 9901 7694 SIP: +1-747-690-5133 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint on multiple tables
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 ([email protected]) >> 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: [email protected]: http://www.rvantwisk.nl/skype: > callto://r.vantwisk > Phone: +1-810-476-4196Cell: +593 9901 7694 SIP: > +1-747-690-5133 > > > > > > > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint on multiple tables
Mario Splivalo 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint on multiple tables
Tom Lane wrote: > Mario Splivalo 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint on multiple tables
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 OR mapper expects. That would enable you to have a proper unique check on the base table, and you OR mapper still sees two tables that it can update. Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] ordered by join? ranked aggregate? how to?
What I need is to join 2 tables CREATE TABLE master( id INT4 ); CREATE TABLE slave ( master_id INT4, rank INT4, value TEXT); What I need is to make the query: SELECT m.id, array_agg(s.value) AS my_problematic_array FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) GROUP BY m.id; return the 'my_problematic_array' in order specified by slave.rank As you probably can guest I don't have any idea know how to do it :/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
