How to show a table in mysql?
In the following mysql commands, which one is correct(suppose you have already login to a database)?
- show tablename;
- describe tablename;
- show table tablename;
- describe table tablename;
The anwser is 2.
How to know if there is any index for a table?
- show index;
- describe index;
- show index from tablename;
- show key from tablename;
The answer is 3.
What is the difference between key and index?
There is no difference at all between key and index. They are synonyms.(http://stackoverflow.com/questions/1401572/what-are-differences-between-index-v-s-key-in-mysql)
The following two commands have the same result:
alter table tablename add key(columname);
alter table tablename add index(columname);
Must a key(index) be unqiue?
No, a key(index) can have duplicate value.
Can a key/index be null?
Yes.
What is the name of a key if I did not name it when creating it?
The name of the key is the name of the column. If you create a composite key,i.e., a key that has multiple columns, the name of the key would be the name of the first column.
How to drop a key/index?
alter table tablename drop key keyname;
If you want to remove/delete the primary key, use alter table tablename drop primary key;
What is a unique key?
A unique key is a key that won’t have duplicate value except duplicate NULL. So the difference between a normal key and a unique key is that normal key can have duplicated value but unique key cannot have.
What is primary key and what is the difference between primary key and unique key?
A primary key is a unique key first, plus a primary key may not have null value.
But if you try to add primary key to column with a null value, the error message is confusing: ERROR 1062 (23000): Duplicate entry ” for key ‘PRIMARY’. It seems you got duplicate nulls, but actually, one null can prevent you from creating the primary key.
Is there a “create index…” command?
No.
Why do I get “specified key was too long, max key length is 767 bytes partial” error?
A primary key has a length limit for the performance consideration. If you specify a utf-8 varchar column as the primary key, the field can have a maximum characters of 255. If the field is longer than 255 characters, you can create a partial primary key for that column as follows:
alter table tablename add primary key(columnname(255));
Or, you can just create an ordinary key for that column:
alter table tablename add key(columnname);
An ordinary key also only uses the first 255 characters for indexing.
