A PRIMARY KEY cannot contain NULL values.
A UNIQUE index can be allowed to contain NULL values.
A UNIQUE index that can't contain NULL is functionally equivalent to a PRIMARY KEY.

A FULLTEXT index is specially designed for text searching.
A SPATIAL index applies only to columns that have spatial data types.

CREATE TABLE t
(
id INT NOT NULL PRIMARY KEY,
name CHAR(30) NOT NULL
);

CREATE TABLE t
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
primary key (id, name)
);

For all index types other than PRIMARY KEY, you can name an index by including the name just before the column list.
Index names are displayed by the SHOW CREATE TABLE or SHOW INDEX statement.


ALTER TABLE t ADD PRIMARY KEY (id), ADD INDEX [index_name] (firstname, lastname);
CREATE UNIQUE INDEX index_name ON t (id);
CREATE INDEX index_name ON t (firstname, lastname);
Note that with CREATE INDEX, it's necessary to provide an index name.
Only ALTER TABLE supports the use of PRIMARY KEY.


MEMORY tables use HASH index by default. HASH index is usable only for comparisons that use the = or <=> operator. Also, changing non-unique indexes is relatively slow.
If a MEMORY table contains non-unique indexes and there will be many duplicate values and will be used with many type of comparison operators, BTEE indexes is better.

ALTER TABLE t ADD INDEX USING BTREE (id);


ALTER TABLE t DROP PRIMARY KEY;
ALTER TABLE t DROP index_name, DROP index_name2;
To drop index, you must specify its name.

DROP INDEX `PRIMARY` ON t;
To drop a PRIMARY KEY with DROP INDEX, use the index name PRIMARY with a quoted identifier because the work 'PRIMARY' is a reserve word.