Re: [SQL] Alternative to Select in table check constraint

2006-07-07 Thread Andreas Joseph Krogh
On Saturday 01 July 2006 03:41, Richard Broersma Jr wrote: > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > FROM BADGES > > > WHERE STATUS = 'A' > > > GROUP BY EMPNO)) > > > > From the P

Re: [SQL] Alternative to Select in table check constraint

2006-07-02 Thread Richard Broersma Jr
> This is more of an implementation option, but when I worry about what is > active/inactive I put start/end dates on the tables. Then you don't need > active indicators. You just look for the record where now() is >= start > date and now() <= end date or end date is null. You can even > activat

Re: [SQL] Alternative to Select in table check constraint

2006-07-02 Thread Richard Broersma Jr
> On Fri, 2006-06-30 at 18:41 -0700, Richard Broersma Jr wrote: > > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > > FROM BADGES > > > > WHERE STATUS = 'A' > > > > GROUP BY EMPNO)) > > >

Re: [SQL] Alternative to Select in table check constraint

2006-07-01 Thread Aaron Bono
This is more of an implementation option, but when I worry about what is active/inactive I put start/end dates on the tables.  Then you don't need active indicators.  You just look for the record where now() is >= start date and now() <= end date or end date is null.  You can even activate/deactiva

Re: [SQL] Alternative to Select in table check constraint

2006-07-01 Thread Rod Taylor
On Fri, 2006-06-30 at 18:41 -0700, Richard Broersma Jr wrote: > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > > FROM BADGES > > > WHERE STATUS = 'A' > > > GROUP BY EMPNO)) > > > > From t

Re: [SQL] Alternative to Select in table check constraint

2006-07-01 Thread Erik Jones
Richard Broersma Jr wrote: But now that I think about it, using the authors suggestion (if it actually worked), how would would it be possible to change the active status from one badge to another? Well, the check constraint as you wrote it requires that there always be a badge listed as act

Re: [SQL] Alternative to Select in table check constraint

2006-06-30 Thread Richard Broersma Jr
> > The following codes doesn't work on PostgreSQL 8.1.4 but according to > > the book does conform to SQL-92. > > > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > FROM BADGES > > WHERE STATUS = 'A' > >

Re: [SQL] Alternative to Select in table check constraint

2006-06-30 Thread Tom Lane
Richard Broersma Jr <[EMAIL PROTECTED]> writes: > The following codes doesn't work on PostgreSQL 8.1.4 but according to > the book does conform to SQL-92. > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > FROM BADGES > WHERE ST

Re: [SQL] Alternative to Select in table check constraint

2006-06-30 Thread Richard Broersma Jr
> > CHECK ( 1 = ALL ( SELECT COUNT(STATUS) > > FROM BADGES > > WHERE STATUS = 'A' > > GROUP BY EMPNO)) > > From the PostgreSQL docs (http://www.postgresql.org/docs/8.1/ > interactive/sql

Re: [SQL] Alternative to Select in table check constraint

2006-06-30 Thread Michael Glaesemann
On Jul 1, 2006, at 6:55 , Richard Broersma Jr wrote: CHECK ( 1 = ALL ( SELECT COUNT(STATUS) FROM BADGES WHERE STATUS = 'A' GROUP BY EMPNO)) From the PostgreSQL docs (http://www.postgr