MySQL ALTER TABLE

Last update on August 19 2022 21:51:22 (UTC/GMT +8 hours)

ALTER TABLE

The ALTER TABLE command is used to change the structure of an existing table. It helps to add or delete columns, create or destroy indexes, change the type of existing columns, rename columns or the table itself. It can also be used to change the comment for the table and type of the table.

Syntax:

ALTER [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO|AS] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | FORCE
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

table_options:
    table_option [[,] table_option] ...  (see CREATE TABLE options)

Arguments:

Name	Description
COLUMN	List of columns.
FIRST	A column can be added at a specific position within a table row, using FIRST or AFTER clause. By default, the column is added at the last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.
INDEX \| KEY	KEY is normally a synonym for INDEX
CONSTRAINT	CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns.
PRIMARY KEY	A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If not declare MySQL declares them so implicitly. A table can have only one PRIMARY KEY.
UNIQUE	A UNIQUE index creates a constraint in which all values in the index must be distinct. An error occurs when you try to add a new row with a key value that matches an existing row.
FULLTEXT	FULLTEXT indexes are used for full-text searches. Only the MyISAM storage engine supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified.
SPATIAL	SPATIAL indexes can be created on spatial data types. Spatial types are supported only for MyISAM tables and indexed columns must be declared as NOT NULL.
FOREIGN KEY	InnoDB and NDB tables support checking of foreign key constraints. The columns of the referenced table must always be explicitly named. Both ON DELETE and ON UPDATE functions on foreign keys.
CHECK	For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements other than the InnoDB and NDB engine. The CHECK clause is parsed but ignored by all storage engines.
ALGORITHM=COPY	You needed this when you are applying ALTER TABLE in earlier versions of MySQL (< 5.6) while altering a table online. This method was used to use a temporary table while altering a table.
ALGORITHM = DEFAULT	is the same a specifying no ALGORITHM clause at all.
ALGORITHM=INPLACE	The ALGORITHM=INPLACE continue the operation inside the InnoDB storage engines by using the in-place technique, and fail which are not support this features with an error.
LOCK = DEFAULT	Permit a series of coincident events i.e. reads and writes when supported. Otherwise permit concurrent reads when supported else enforce exclusive access.
LOCK = NONE	When supported, permit concurrent reads and writes else return an error message.
LOCK =SHARED	When supported, allow concurrent reads but restrict writes. Remember that writes will be blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. When concurrent reads are not supported an error message will be returned.
LOCK = EXCLUSIVE	This enforce exclusive access. It happens even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.

Basic Examples

Create a table testtable as shown below:

CREATE TABLE testtable (col1 INT(11), col2 VARCHAR(15));

To rename the table from testtable to w3r1, use the following statement.

ALTER TABLE testtable RENAME w3r1;

To change column col1 from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from VARCHAR(15) to CHAR(25) as well as renaming it from col2 to col3, the following statement can be used.

ALTER TABLE w3r1 MODIFY col1 TINYINT NOT NULL, CHANGE col2 col3 VARCHAR(25);

To add a new TIMESTAMP column named col4, the following statement can be used.

ALTER TABLE w3r1 ADD col4 TIMESTAMP;

To add an index on column col4 and a UNIQUE index on column col1, the following statement can be used.

ALTER TABLE w3r1 ADD INDEX (col4), ADD UNIQUE (col1);

To remove column col3 from the table w3r1, the following statement can be used.

ALTER TABLE w3r1 DROP COLUMN col3;

To add a new AUTO_INCREMENT integer column named col3, the following statement can be used.

ALTER TABLE w3r1 ADD col3 INT UNSIGNED NOT NULL AUTO_INCREMENT,   
ADD PRIMARY KEY (col3);

Here in the above example, we indexed col3 (as a PRIMARY KEY) because AUTO_INCREMENT columns must be indexed, and we declare col3 as NOT NULL because primary key columns cannot be NULL.

To change the data type of col1 into BIGINT, the following statement can be used.

ALTER TABLE w3r1 MODIFY col1 BIGINT;

If you want to include the attributes UNSIGNED DEFAULT 1 and COMMENT 'test column', show the below statement -

ALTER TABLE w3r1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'test column';

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE w3r1 CONVERT TO CHARACTER SET latin1;

MySQL ALTER TABLE insert column

Here is the structure of newbook_mast table.

Sample Output:

+------------+--------------+------+-----+------------+-------+
| Field      | Type         | Null | Key | Default    | Extra |
+------------+--------------+------+-----+------------+-------+
| book_id    | varchar(15)  | NO   | PRI |            |       |
| book_name  | varchar(50)  | NO   |     |            |       |
| isbn_no    | varchar(15)  | NO   |     |            |       |
| cate_id    | varchar(8)   | NO   |     |            |       |
| aut_id     | varchar(8)   | NO   |     |            |       |
| pub_id     | varchar(8)   | NO   |     |            |       |
| dt_of_pub  | date         | NO   |     | 0000-00-00 |       |
| pub_lang   | varchar(15)  | YES  |     | NULL       |       |
| no_page    | decimal(5,0) | NO   |     | 0          |       |
| book_price | decimal(8,2) | NO   |     | 0.00       |       |
+------------+--------------+------+-----+------------+-------+
10 rows in set (0.00 sec)

If you want to add a column 'id' of integer type in the table newbook_mast, the following statement can be used.

ALTER TABLE newbook_mast
ADD id  INT;

Here is the structure of the newbook_mast after add a column id .

MySQL ALTER TABLE insert column FIRST

Here is the structure of newbook_mast table.

Sample Output:

+------------+--------------+------+-----+------------+-------+
| Field      | Type         | Null | Key | Default    | Extra |
+------------+--------------+------+-----+------------+-------+
| book_id    | varchar(15)  | NO   | PRI |            |       |
| book_name  | varchar(50)  | NO   |     |            |       |
| isbn_no    | varchar(15)  | NO   |     |            |       |
| cate_id    | varchar(8)   | NO   |     |            |       |
| aut_id     | varchar(8)   | NO   |     |            |       |
| pub_id     | varchar(8)   | NO   |     |            |       |
| dt_of_pub  | date         | NO   |     | 0000-00-00 |       |
| pub_lang   | varchar(15)  | YES  |     | NULL       |       |
| no_page    | decimal(5,0) | NO   |     | 0          |       |
| book_price | decimal(8,2) | NO   |     | 0.00       |       |
+------------+--------------+------+-----+------------+-------+
10 rows in set (0.00 sec)

If you want to insert a column id of integer type, as first column of the table newbook_mast, the following statement can be used.

ALTER TABLE newbook_mast
ADD id  INT  FIRST ;

Here is the structure of the newbook_mast after adding a column id at first.

MySQL ALTER TABLE to insert column AFTER a column

Here is the structure of newbook_mast table.

+------------+--------------+------+-----+------------+-------+
| Field      | Type         | Null | Key | Default    | Extra |
+------------+--------------+------+-----+------------+-------+
| book_id    | varchar(15)  | NO   | PRI |            |       |
| book_name  | varchar(50)  | NO   |     |            |       |
| isbn_no    | varchar(15)  | NO   |     |            |       |
| cate_id    | varchar(8)   | NO   |     |            |       |
| aut_id     | varchar(8)   | NO   |     |            |       |
| pub_id     | varchar(8)   | NO   |     |            |       |
| dt_of_pub  | date         | NO   |     | 0000-00-00 |       |
| pub_lang   | varchar(15)  | YES  |     | NULL       |       |
| no_page    | decimal(5,0) | NO   |     | 0          |       |
| book_price | decimal(8,2) | NO   |     | 0.00       |       |
+------------+--------------+------+-----+------------+-------+
10 rows in set (0.00 sec)

If you want to add two specific columns pub_name and pub_add after pub_id and dt_of_pub columns respectively, the following statement can be used.

ALTER TABLE newbook_mast
ADD pub_name  VARCHAR(35)  AFTER pub_id,
ADD pub_add   VARCHAR(50)  AFTER dt_of_pub;

Here is the structure of the newbook_mast after add two columns in specific position said above.

MySQL ALTER TABLE ADD INDEX

.If you want to add an index named 'cate_id' on 'cate_id' column for the table 'newbook_mast', the following statement can be used.

ALTER TABLE newbook_mast
ADD INDEX cate_id(cate_id);

Here is the indexes for the newbook_mast table after adding an index named cate_id on cate_id column.

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| newbook_mast |          0 | PRIMARY  |            1 | book_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| newbook_mast |          1 | cate_id  |            1 | cate_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

MySQL ALTER TABLE ADD UNIQUE INDEX

If you want to add a UNIQUE INDEX named 'cate_id' on 'cate_id' column for the table 'newbook_mast', the following statement can be used.

ALTER TABLE newbook_mast
ADD UNIQUE INDEX cate_id(cate_id);

Here is the unique indexes for the newbook_mast table after adding an unique index named cate_id on cate_id column.

Sample Output:

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| newbook_mast |          0 | PRIMARY  |            1 | book_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| newbook_mast |          0 | cate_id  |            1 | cate_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

MySQL ALTER TABLE ADD PRIMARY KEY

Here is the structure of tstpurch table. The below figure shows that it has no primary key.

+-------------+---------------+------+-----+------------+-------+
| Field       | Type          | Null | Key | Default    | Extra |
+-------------+---------------+------+-----+------------+-------+
| invoice_no  | varchar(12)   | NO   |     |            |       |
| invoice_dt  | date          | NO   |     | 0000-00-00 |       |
| ord_no      | varchar(25)   | NO   |     | NULL       |       |
| ord_date    | date          | NO   |     | 0000-00-00 |       |
| receive_dt  | date          | NO   |     | 0000-00-00 |       |
| book_id     | varchar(8)    | NO   |     |            |       |
| book_name   | varchar(50)   | NO   |     |            |       |
| pub_lang    | varchar(8)    | YES  |     | NULL       |       |
| cate_id     | varchar(8)    | YES  |     | NULL       |       |
| receive_qty | int(5)        | NO   |     | 0          |       |
| purch_price | decimal(12,2) | NO   |     | 0.00       |       |
| total_cost  | decimal(12,2) | NO   |     | 0.00       |       |
+-------------+---------------+------+-----+------------+-------+
12 rows in set (0.01 sec)

If you want to l create a PRIMARY KEY on invoice_no column for the table tstpurch, the following statement can be used.

ALTER TABLE tstpurch
ADD PRIMARY KEY invoice_no (invoice_no);

Here is the primary key after adding a primary key named invoice_no on invoice_no column.

+-------------+---------------+------+-----+------------+-------+
| Field       | Type          | Null | Key | Default    | Extra |
+-------------+---------------+------+-----+------------+-------+
| invoice_no  | varchar(12)   | NO   | PRI |            |       |
| invoice_dt  | date          | NO   |     | 0000-00-00 |       |
| ord_no      | varchar(25)   | NO   |     | NULL       |       |
| ord_date    | date          | NO   |     | 0000-00-00 |       |
| receive_dt  | date          | NO   |     | 0000-00-00 |       |
| book_id     | varchar(8)    | NO   |     |            |       |
| book_name   | varchar(50)   | NO   |     |            |       |
| pub_lang    | varchar(8)    | YES  |     | NULL       |       |
| cate_id     | varchar(8)    | YES  |     | NULL       |       |
| receive_qty | int(5)        | NO   |     | 0          |       |
| purch_price | decimal(12,2) | NO   |     | 0.00       |       |
| total_cost  | decimal(12,2) | NO   |     | 0.00       |       |
+-------------+---------------+------+-----+------------+-------+
12 rows in set (0.01 sec)

Here is the details of the index.

Sample Output:

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tstpurch |          0 | PRIMARY  |            1 | invoice_no  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

MySQL ALTER TABLE ADD FOREIGN KEY

Here are the structure of torder table and newbook_mast table.

Table: torder  
+-----------+-------------+------+-----+------------+-------+
| Field     | Type        | Null | Key | Default    | Extra |
+-----------+-------------+------+-----+------------+-------+
| ord_no    | varchar(15) | NO   |     |            |       |
| ord_date  | date        | NO   |     | 0000-00-00 |       |
| book_id   | varchar(15) | NO   | PRI |            |       |
| book_name | varchar(50) | NO   |     |            |       |
| cate_id   | varchar(8)  | NO   | PRI |            |       |
| pub_lang  | varchar(15) | NO   |     |            |       |
| ord_qty   | int(5)      | NO   |     | 0          |       |
+-----------+-------------+------+-----+------------+-------+

Table: newbook_mast
+------------+--------------+------+-----+------------+-------+
| Field      | Type         | Null | Key | Default    | Extra |
+------------+--------------+------+-----+------------+-------+
| book_id    | varchar(15)  | NO   | PRI |            |       |
| book_name  | varchar(50)  | NO   |     |            |       |
| isbn_no    | varchar(15)  | NO   |     |            |       |
| cate_id    | varchar(8)   | NO   |     |            |       |
| aut_id     | varchar(8)   | NO   |     |            |       |
| pub_id     | varchar(8)   | NO   |     |            |       |
| dt_of_pub  | date         | NO   |     | 0000-00-00 |       |
| pub_lang   | varchar(15)  | YES  |     | NULL       |       |
| no_page    | decimal(5,0) | NO   |     | 0          |       |
| book_price | decimal(8,2) | NO   |     | 0.00       |       |
+------------+--------------+------+-----+------------+-------+
10 rows in set (0.00 sec)

If you want to create a FOREIGN KEY with the combination of book_id and cate_id columns of newbook_mast table with a reference from torder table, the following statement can be used.

ALTER TABLE newbook_mast
ADD  FOREIGN KEY(book_id,cate_id)
REFERENCES 
torder(book_id,cateid);

Here is the details of the index of newbook_mast table.

+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| newbook_mast |          0 | PRIMARY  |            1 | book_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| newbook_mast |          1 | book_id  |            1 | book_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| newbook_mast |          1 | book_id  |            2 | cate_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

MySQL ALTER TABLE ADD and DROP column, INDEX, PRIMARY KEY and FOREIGN KEY

Here is the structure and index of tstpurch and torder table.

Table: tstpurch
+-------------+---------------+------+-----+------------+-------+
| Field       | Type          | Null | Key | Default    | Extra |
+-------------+---------------+------+-----+------------+-------+
| id          | int(5)        | YES  |     | NULL       |       |
| invoice_no  | varchar(12)   | NO   | PRI |            |       |
| invoice_dt  | date          | NO   |     | 0000-00-00 |       |
| ord_no      | varchar(25)   | NO   | MUL | NULL       |       |
| ord_date    | date          | NO   |     | 0000-00-00 |       |
| receive_dt  | date          | NO   |     | 0000-00-00 |       |
| book_id     | varchar(8)    | NO   |     |            |       |
| book_name   | varchar(50)   | NO   |     |            |       |
| pub_lang    | varchar(8)    | YES  |     | NULL       |       |
| cate_id     | varchar(8)    | YES  | MUL | NULL       |       |
| receive_qty | int(5)        | NO   |     | 0          |       |
| purch_price | decimal(12,2) | NO   |     | 0.00       |       |
| total_cost  | decimal(12,2) | NO   |     | 0.00       |       |
+-------------+---------------+------+-----+------------+-------+

Indexes of  tstpurch table
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tstpurch |          0 | PRIMARY  |            1 | invoice_no  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tstpurch |          1 | cate_id  |            1 | cate_id     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| tstpurch |          1 | ord_no   |            1 | ord_no      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Table: torder
+-----------+-------------+------+-----+------------+-------+
| Field     | Type        | Null | Key | Default    | Extra |
+-----------+-------------+------+-----+------------+-------+
| ord_no    | varchar(15) | NO   | PRI |            |       |
| ord_date  | date        | NO   |     | 0000-00-00 |       |
| book_id   | varchar(15) | NO   | MUL |            |       |
| book_name | varchar(50) | NO   |     |            |       |
| cate_id   | varchar(8)  | NO   |     |            |       |
| pub_lang  | varchar(15) | NO   |     |            |       |
| ord_qty   | int(5)      | NO   |     | 0          |       |
+-----------+-------------+------+-----+------------+-------+

Indexes of torder
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| torder |          0 | PRIMARY  |            1 | ord_no      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| torder |          1 | book_id  |            1 | book_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| torder |          1 | book_id  |            2 | cate_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

If you want to modifies the structure of 'tstpurch' table in the following manner -

1. Drop the 'id' column, existing primary key, index 'cate_id' and foreign key 'ord_no'.
2. Add an integer type column 'id' with default value 0.
3. Add column 'chano' which is varchar type and size 10 and don't accept any NULL value.
4. Add a date type column 'chadt'.
5. Add a primary key named 'invoice_no' on 'invoice_no' column.
6. Add an index named 'cate_id' on 'cate_id' column.
7. Add a foreign key in combination of two columns 'ord_no' and 'book_id' which is referred by the same primary key of 'torder' table.

the following statement can be used.

ALTER TABLE tstpurch
DROP id,
ADD id int NOT NULL DEFAULT 0,
ADD chano VARCHAR(10) NOT NULL,
ADD  chadt date,
DROP  PRIMARY KEY,
ADD  PRIMARY KEY invoice_no (invoice_no),
DROP  INDEX cate_id,
ADD  INDEX cate_id(cate_id),
DROP  FOREIGN KEY ord_no,
ADD  FOREIGN KEY(book_id,cate_id) REFERENCES
torder(book_id,cate_id);

and now, here is the structure and index of tstpurch and torder table

Table: tstpurch
+-------------+---------------+------+-----+------------+-------+
| Field       | Type          | Null | Key | Default    | Extra |
+-------------+---------------+------+-----+------------+-------+
| invoice_no  | varchar(12)   | NO   | PRI |            |       |
| invoice_dt  | date          | NO   |     | 0000-00-00 |       |
| ord_no      | varchar(25)   | NO   | MUL | NULL       |       |
| ord_date    | date          | NO   |     | 0000-00-00 |       |
| receive_dt  | date          | NO   |     | 0000-00-00 |       |
| book_id     | varchar(8)    | NO   | MUL |            |       |
| book_name   | varchar(50)   | NO   |     |            |       |
| pub_lang    | varchar(8)    | YES  |     | NULL       |       |
| cate_id     | varchar(8)    | YES  | MUL | NULL       |       |
| receive_qty | int(5)        | NO   |     | 0          |       |
| purch_price | decimal(12,2) | NO   |     | 0.00       |       |
| total_cost  | decimal(12,2) | NO   |     | 0.00       |       |
| id          | int(11)       | NO   |     | 0          |       |
| chano       | varchar(10)   | NO   |     | NULL       |       |
| chadt       | date          | YES  |     | NULL       |       |
+-------------+---------------+------+-----+------------+-------+

Indexes of tstpurch
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tstpurch |          0 | PRIMARY  |            1 | invoice_no  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tstpurch |          1 | ord_no   |            1 | ord_no      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| tstpurch |          1 | cate_id  |            1 | cate_id     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| tstpurch |          1 | book_id  |            1 | book_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| tstpurch |          1 | book_id  |            2 | cate_id     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Table: torder
+-----------+-------------+------+-----+------------+-------+
| Field     | Type        | Null | Key | Default    | Extra |
+-----------+-------------+------+-----+------------+-------+
| ord_no    | varchar(15) | NO   | PRI |            |       |
| ord_date  | date        | NO   |     | 0000-00-00 |       |
| book_id   | varchar(15) | NO   | MUL |            |       |
| book_name | varchar(50) | NO   |     |            |       |
| cate_id   | varchar(8)  | NO   |     |            |       |
| pub_lang  | varchar(15) | NO   |     |            |       |
| ord_qty   | int(5)      | NO   |     | 0          |       |
+-----------+-------------+------+-----+------------+-------+

Indexes of torder
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| torder |          0 | PRIMARY  |            1 | ord_no      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| torder |          1 | book_id  |            1 | book_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| torder |          1 | book_id  |            2 | cate_id     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Previous: MySQL CREATE INDEX
Next: MySQL Partitioning