MySQL Indexes, Primary Keys, Foreign Keys, and Storage Engine Configuration Guide

(1) Create a table and specify INDEX columns

CREATE TABLE tea4( id CHAR(6) NOT NULL, name VARCHAR(4) NOT NULL, age INT(3) NOT NULL, gender ENUM('boy','girl') DEFAULT 'boy', INDEX(id), INDEX(name) );

(2) Drop an existing INDEX from a table

DROP INDEX name ON tea4; // delete index on column name

(3) Add an INDEX to an existing table

For example, create an index named nianling on the age column of tea4 :

CREATE INDEX nianling ON tea4(age);

(4) View index information of a table

SHOW INDEX FROM tea4\G;

(5) Create a table with UNIQUE indexes

UNIQUE enforces uniqueness; a table can have multiple UNIQUE columns. Example creating tea5 with UNIQUE on id and name , and a regular INDEX on age :

CREATE TABLE tea5( id CHAR(6), name VARCHAR(4) NOT NULL, age INT(3) NOT NULL, UNIQUE(id), UNIQUE(name), INDEX(age) );

(6) Drop a UNIQUE index (same syntax as dropping a normal INDEX)

DROP INDEX name ON tea5; // remove UNIQUE index on name

(7) Re‑create a UNIQUE index on name and verify

CREATE UNIQUE INDEX name ON tea5(name);

(8) Define a PRIMARY KEY when creating a table

You can declare the primary key inline or at the end of the column list:

CREATE TABLE biao01( id INT(4) PRIMARY KEY, // inline definition name VARCHAR(8) );

or

CREATE TABLE biao02( id INT(4), name VARCHAR(8), PRIMARY KEY(id) // defined after all columns );

(9) Add AUTO_INCREMENT to an integer primary key

CREATE TABLE tea6( id INT(4) AUTO_INCREMENT, name VARCHAR(4) NOT NULL, age INT(2) NOT NULL, PRIMARY KEY(id) );

(10) Drop an existing PRIMARY KEY

ALTER TABLE biao01 DROP PRIMARY KEY;

(11) Error when dropping a PRIMARY KEY that has AUTO_INCREMENT

Attempting ALTER TABLE tea6 DROP PRIMARY KEY; yields:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Solution: first remove the AUTO_INCREMENT attribute, then drop the key:

ALTER TABLE tea6 MODIFY id INT(4) NOT NULL;

ALTER TABLE tea6 DROP PRIMARY KEY;

(12) Add a PRIMARY KEY to an existing table

ALTER TABLE tea6 ADD PRIMARY KEY(id);

Step Two: Create database tables and set foreign keys for cascade update/delete

1) Create yg table (employees) with auto‑increment primary key

CREATE TABLE yg( yg_id INT(4) AUTO_INCREMENT, name CHAR(16) NOT NULL, PRIMARY KEY(yg_id) );

2) Create gz table (salaries) with a foreign key referencing yg

CREATE TABLE gz( gz_id INT(4) NOT NULL, name CHAR(16) NOT NULL, gz FLOAT(7,2) NOT NULL DEFAULT 0, INDEX(name), FOREIGN KEY(gz_id) REFERENCES yg(yg_id) ON UPDATE CASCADE ON DELETE CASCADE );

3) Insert two employee records into yg

INSERT INTO yg(name) VALUES('Jerry'),('Tom');

4) Insert two salary records into gz

INSERT INTO gz(gz_id,name,gz) VALUES(1,'Jerry',12000),(2,'Tom',8000);

(5) Verify UPDATE cascade

Change Jerry's yg_id to 1234:

UPDATE yg SET yg_id=1234 WHERE name='Jerry';

The change is reflected automatically in gz because of the cascade.

(6) Verify DELETE cascade

Delete Jerry from yg :

DELETE FROM yg WHERE name='Jerry';

The corresponding row in gz is also removed.

(7) Drop the foreign‑key constraint

First obtain the constraint name (e.g., gz_ibfk_1 ) via SHOW CREATE TABLE gz\G , then:

ALTER TABLE gz DROP FOREIGN KEY gz_ibfk_1;

Three: MySQL storage engine configuration

Change the default storage engine:

SET default_storage_engine=MyISAM; // switch to MyISAM

To make the change permanent, edit /etc/my.cnf under the [mysqld] section and restart MySQL:

# vim /etc/my.cnf [mysqld] ... default_storage_engine=MEMORY // switch to MEMORY engine # service mysql restart

After testing, restore the original engine (typically InnoDB) and verify with:

mysql -u root -p -e "SHOW VARIABLES LIKE 'default_storage_engine';"