and why are they needed? (especially lines: UNIQUE KEY `block_ID` (`block_ID`), and
KEY `block_ID_2` (`block_ID`)) .
I kind of understand primary key thing ( PRIMARY KEY (`block_ID`),) and maybe UNIQUE KEY `block_ID` (`block_ID`) also (though I don't understand why `block_ID` (`block_ID`) is needed, why not just UNIQUE KEY (`block_ID`), ), but why would one use KEY `block_ID_2` (`block_ID`)? What does one achieve with this KEY.... line? And are there any alternatives to this approach for implementing the same thing (that I would undestand better ) ?
O, did I mention that I'm new to DB and MySQL (as my noob questions don't reflect that ).
Could some one please shed some light on this key usage in MySQL or at least point me to some good source on web, where this things are well explained.
Since block_ID is defined as a primary key you do not need to declare it as unique. It is automatically made unique. Additionally you do not need the key declaration.
When you define a column as a primary key it means that it is automatically indexed and made unique
INDEX 'block_ID_2' ('block_ID') [or INDEX ('block_ID') at least], would be equal to KEY 'block_ID_2' ('block_ID') then?
If so, do you know why two different keyword [INDEX and KEY; legacy of previos versions maybe?].
Also, do you have any idea why would one use UNIQE KEY and KEY in such fashion if PRIMARY KEY does that [good programming practice, maybe?]. I'm asking this because I saw some examples of CREATE TABLE, that use same syntax.
And oh, one more thing, when I use KEY 'block_ID_2' ('block_ID') like this, I create index for block_ID field and name this index block_ID_2, right? Or am I totally wrong?