On 07/30/2009 02:23 PM, Joerg Bruehe wrote:
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.
No, PK I understand.
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).
This is what I was thinking, also.
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.
Yes, I treat a PK as being something that's over and above simply being
unique. But this touches upon the reason for my question: I guess I was
wondering if they might be subtly different, though "merging" the two
would result in a PK :-)
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.
Thanks, both, for your replies.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
