Re: Unique Keys

Mikito Harakiri wrote:
> "Tony Andrews" <andrewst_at_onetel.com> wrote in message
news:<1101387601.990897.214300_at_f14g2000cwb.googlegroups.com>...
> > a unique constraint isn't primitive, it is a
> > shorthand for a constraint that could be more generally expressed
as:
> >
> > (select 0 from the_table t1, the_table t2
> > where t1.primary_key != t2.primary_key
> > and t1.unique_key = t2.unique_key)
> > ={}
>
> "primary_key" is a typo right? You probably have meant:
>
> select 0 from the_table t1, the_table t2
> where t1.unique_key = t2.unique_key
> and ( t1.col1 != t2.t1.col1
> or t1.col2 != t2.t1.col2
> or ....)
> ={}
>
> 2 more cents. This definition is fine for sets but doesn't work for
> bags. Surprisingly bag definition is more elegant:
>
> select 0 from the_table
> group by unique_key
> having count(1) > 1
> = {}

Yes, I got it wrong. It was the fact that SQL uses bags, coupled with my attempt to use the form that only works with sets that confounded me! I found myself wanting to resort to vendor-specific features like Oracle's ROWID to identify 2 rows as not being "the same row". Since I was defining a UNIQUE constraint, I cheated by assuming the table must already have a PRIMARY KEY constraint. Received on Fri Nov 26 2004 - 11:27:25 CET