For those cases, assigning a useless unique key just to comply with some relational religion just adds index update overhead on every update / insert with no semantic gain.
Until all SQL engines support SQL2003 nested sub-tables, or they all come up with array support (and I'm not holding my breath for either of those), the easiest way to handle this is a secondary table without a PK.
It's a corner case, but to disallow it completely seems kind of draconian, and could hinder adoption in those cases where someone may be stuck with an existing schema.
Thanks for the rope, Mike :-)
Rick
On 2/17/06, Ed Suominen <[EMAIL PROTECTED]> wrote:
Yeah, I think Jonathan is right after all. He said it very well.
Open mouth, insert foot. :-)
Michael Bayer wrote:
>
> On Feb 17, 2006, at 1:55 PM, Jonathan Ellis wrote:
>
>> IMO, no. This is a broken table. Your db may allow it but from a
>> relational standpoint this is simply broken.
>>
>> There are a couple ways you could fix it. One is simply a unique key
>> on (id, note). If you really wanted to allow duplicate texts, adding
>> a time_entered field and create the unique key over all three might
>> make sense. But designing for indistinguishable, duplicate rows is
>> broken; relational theory is set-based. Ignoring this leads to
>> ugliness and pain.
>>
>
> heh....id have said something similar but im on my anti-harsh meds this
> week :) Ive no problem with SA letting people do what they want but
> yah, primary keys are pretty important !
>
