Re: Teach SELECT DISTINCT first!

"Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message news:1tVjc.30$WN3.212_at_news.oracle.com...
> > "robert" <gnuoytr_at_rcn.com> wrote in message
> > news:da3c2186.0404271241.a427ea5_at_posting.google.com...
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=c0d87ec0
>

.0202240850.428d7b88%40posting.google.com&rnum=12&prev=/groups%3Fq%3D%252B%2
>

522select%2Bdistinct%2522%2B%252Bdate%2B%2Bgroup:comp.databases.theory%26sta
>

rt%3D10%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases
>

.theory%26selm%3Dc0d87ec0.0202240850.428d7b88%2540posting.google.com%26rnum%
> 3D12
>
> <quote>
> Consider a table for a school schedule:
>
> CREATE TABLE Schedule
> (period INTEGER NOT NULL,
> teacher CHAR(15) NOT NULL,
> room INTEGER NOT NULL,
> CONSTRAINT tr UNIQUE (teacher, room), -- candidate keys
> CONSTRAINT pr UNIQUE (period, room),
> CONSTRAINT pt UNIQUE (period, teacher),
> CONSTRAINT ptr UNIQUE (period, teacher, room));
>
[snip]
>
> Doesn't
>
> CONSTRAINT pt UNIQUE (period, teacher)
>
> (or 'tr', or 'pr' for that matter) imply
>
> CONSTRAINT ptr UNIQUE (period, teacher, room));
>
> so that constraint 'ptr' is simply redundant?

Yep, which is the reason why a key (whether 'primary' or not) should not be a super key. Taking about "shorter keys becoming smaller indexes, which will search faster" is rather beside the point.

Question, should a good DMBS disallow or merely somehow discourage (the definition of) redundant constraints?

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Thu Apr 29 2004 - 13:33:23 CEST