Hi !
mos wrote:
> At 09:13 AM 7/30/2009, b wrote:
>> Are UNIQUE KEY & UNIQUE INDEX two ways of specifying the same thing?
>> If not, what are the differences?
>>
>> Feel free to tell me to RTFM but please post manual chapters. I've
>> been looking but haven't been able to find anything.
>
> They are the same thing. If you meant "Primary Key" and "Unique Index"
> then they too are basically the same except Primary Key is NOT NULL and
> the optimizer will infer the primary key when doing RI joins if an index
> is not specified.
>
> There may be other subtle differences that others can comment on.
For all practical purposes, I agree.
From the database (or SQL) theory point of view, a "unique constraint"
is a logical concept ("there cannot be any two cars having the same
registration number"), and any index is a physical means of
implementation (to speed up access).
I have never heard of any DBMS implementing a unique constraint in any
way but using a "unique index", but it could be done: at a huge
performance cost (sequential search through the table).
For "primary key", there is also the school that holds a primary key
cannot change, it is a permanent attribute. If you share that idea, then
there is a difference to the uniqueness concept.
Take the example of a car: It has a manufacturer number which is
permanent, but the registration will change when it is moved (or sold)
to another area or country (depending on local law).
So the manufacturer number could be used as the primary key (will never
change), whereas the registration is guaranteed to be unique (at any
time) but may vary over time.
Regards,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
