MySQL ALTER TABLE

Last update on April 20 2024 12:08:49 (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.


-- Rename the table named "testtable" to "w3r1"
ALTER TABLE testtable RENAME w3r1;

Explanation:

This SQL statement alters the table structure by renaming an existing table.

ALTER TABLE is a SQL statement used to modify the structure of a table, such as adding, modifying, or dropping columns, constraints, or indexes.

testtable is the current name of the table that you want to rename.

RENAME w3r1 specifies the new name for the table. In this case, the table will be renamed to "w3r1".

Renaming a table can be useful for organizational purposes or to better reflect its contents or purpose. It does not affect the data stored in the table, only the table's metadata.

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.


-- Modify the column named "col1" in the table "w3r1" to have data type TINYINT and be marked as NOT NULL
ALTER TABLE w3r1 MODIFY col1 TINYINT NOT NULL;
-- Change the name of the column "col2" to "col3" in the table "w3r1" and modify its data type to VARCHAR(25)
ALTER TABLE w3r1 CHANGE col2 col3 VARCHAR(25);

Explanation:

These SQL statements are used to modify the structure of the table named "w3r1".

ALTER TABLE is a SQL statement used to modify the structure of a table.

In the first statement:

MODIFY is used to change the data type and nullability constraints of an existing column.

col1 is the name of the column being modified.

TINYINT is the new data type for the column, which stores small integer values.

NOT NULL ensures that the column cannot contain NULL values.

In the second statement:

CHANGE is used to rename a column and modify its data type simultaneously.

col2 is the current name of the column being changed.

col3 is the new name for the column.

VARCHAR(25) specifies the new data type for the column, which is a variable-length string with a maximum length of 25 characters.

These alterations affect the structure of the table but do not modify the data stored in the table.

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


-- Add a new column named "col4" to the table "w3r1" with data type TIMESTAMP
ALTER TABLE w3r1 ADD col4 TIMESTAMP;

Explanation:

This SQL statement alters the structure of the table named "w3r1" by adding a new column.

ALTER TABLE is a SQL statement used to modify the structure of a table.

w3r1 is the name of the table being altered.

ADD col4 TIMESTAMP specifies the action to be performed:

ADD indicates that a new column is being added to the table.

col4 is the name of the new column being added.

TIMESTAMP is the data type of the new column, which represents date and time values in MySQL. It is commonly used to store the date and time when a record is created or modified.

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


-- Add an index on the column "col4" to the table "w3r1"
ALTER TABLE w3r1 ADD INDEX (col4),
-- Add a unique constraint on the column "col1" to ensure its values are unique across the table "w3r1"
ADD UNIQUE (col1);

Explanation:

These SQL statements alter the structure of the table named "w3r1" by adding an index and a unique constraint.

ALTER TABLE is a SQL statement used to modify the structure of a table.

w3r1 is the name of the table being altered.

The first statement adds an index on the column "col4":

ADD INDEX (col4) specifies the action to be performed.

INDEX (col4) indicates that an index is being added on the column "col4".

This index will improve the performance of queries that involve searching or sorting by the values in the "col4" column.

The second statement adds a unique constraint on the column "col1":

ADD UNIQUE (col1) specifies the action to be performed.

UNIQUE (col1) indicates that a unique constraint is being added on the column "col1".

This constraint ensures that all values in the "col1" column are unique across the table, meaning no two rows can have the same value in the "col1" column.

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


-- Remove the column named "col3" from the table "w3r1"
ALTER TABLE w3r1 DROP COLUMN col3;

Explanation:

This SQL statement alters the structure of the table named "w3r1" by removing a column.

ALTER TABLE is a SQL statement used to modify the structure of a table.

w3r1 is the name of the table being altered.

DROP COLUMN col3 specifies the action to be performed:

DROP COLUMN indicates that a column is being removed from the table.

col3 is the name of the column being dropped.

This action removes the "col3" column and all of its associated data from the table "w3r1".

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


-- Add a new column named "col3" to the table "w3r1" with data type INT UNSIGNED, marked as NOT NULL, and auto-incremented
ALTER TABLE w3r1 ADD col3 INT UNSIGNED NOT NULL AUTO_INCREMENT,
-- Add a primary key constraint on the column "col3" to ensure its uniqueness and serve as the primary identifier for each row
ADD PRIMARY KEY (col3);

Explanation:

These SQL statements alter the structure of the table named "w3r1" by adding a new column and defining it as the primary key.

ALTER TABLE is a SQL statement used to modify the structure of a table.

w3r1 is the name of the table being altered.

The first statement adds a new column named "col3" to the table:

ADD col3 INT UNSIGNED NOT NULL AUTO_INCREMENT specifies the action to be performed.

INT UNSIGNED indicates that the data type of the column is an unsigned integer.

NOT NULL ensures that the column cannot contain NULL values.

AUTO_INCREMENT specifies that the column should automatically increment its value for each new row inserted into the table.

The second statement adds a primary key constraint on the column "col3":

ADD PRIMARY KEY (col3) specifies the action to be performed.

PRIMARY KEY (col3) indicates that the "col3" column is being designated as the primary key for the table.

This primary key constraint ensures that each value in the "col3" column is unique, serving as the primary identifier for each row in the table.

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.


-- Modify the data type of the column "col1" in the table "w3r1" to BIGINT
ALTER TABLE w3r1 MODIFY col1 BIGINT;

Explanation:

This SQL statement alters the structure of the table named "w3r1" by modifying the data type of a column.

ALTER TABLE is a SQL statement used to modify the structure of a table.

w3r1 is the name of the table being altered.

MODIFY col1 BIGINT specifies the action to be performed:

MODIFY indicates that the existing column is being modified.

col1 is the name of the column being modified.

BIGINT specifies the new data type for the column, which is a large integer type capable of storing very large numbers.

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


-- Modify the column "col1" in the table "w3r1" to have data type BIGINT UNSIGNED, default value of 1, and a comment of 'test column'
ALTER TABLE w3r1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'test column';

Explanation:

This SQL statement alters the structure of the table named "w3r1" by modifying the attributes of a column.

ALTER TABLE is a SQL statement used to modify the structure of a table.

w3r1 is the name of the table being altered.

MODIFY col1 specifies the action to be performed:

MODIFY indicates that the existing column is being modified.

col1 is the name of the column being modified.

BIGINT UNSIGNED specifies the new data type for the column, which is a large unsigned integer type capable of storing very large positive numbers.

DEFAULT 1 specifies that the default value for the column will be 1 if no value is provided during insertion.

COMMENT 'test column' adds a comment to the column, providing additional information about its purpose or usage. In this case, the comment indicates that the column is for testing purposes.

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


-- Convert the character set of the table "w3r1" to Latin1
ALTER TABLE w3r1 CONVERT TO CHARACTER SET latin1;

Explanation:

This SQL statement alters the character set of the table named "w3r1" to Latin1.

ALTER TABLE is a SQL statement used to modify the structure or properties of a table.

w3r1 is the name of the table being altered.

CONVERT TO CHARACTER SET latin1 specifies the action to be performed:

CONVERT TO CHARACTER SET indicates that the character set of the table is being converted.

latin1 specifies the target character set to which the table will be converted. Latin1, also known as ISO 8859-1, is a character encoding standard that supports most Western European languages. It uses a single byte per character.

This action changes the character set used by the table, affecting how text data is stored and interpreted. It can be useful if you need to support different languages or if you want to match the character set used by other components in your system.

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.


-- Add a new column named "id" to the table "newbook_mast" with data type INT
ALTER TABLE newbook_mast ADD id INT;

Explanation:

This SQL statement alters the structure of the table named "newbook_mast" by adding a new column.

ALTER TABLE is a SQL statement used to modify the structure of a table.

newbook_mast is the name of the table being altered.

ADD id INT specifies the action to be performed:

ADD indicates that a new column is being added to the table.

id is the name of the new column being added.

INT specifies the data type of the new column, which is an integer type capable of storing whole numbers.

This action adds a new column named "id" to the "newbook_mast" table with the default data type of INT. The column will initially have no data until values are inserted into it.

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.


-- Add a new column named "id" to the beginning of the table "newbook_mast" with data type INT
ALTER TABLE newbook_mast ADD id INT FIRST;

Explanation:

This SQL statement alters the structure of the table named "newbook_mast" by adding a new column at the beginning of the table.

ALTER TABLE is a SQL statement used to modify the structure of a table.

newbook_mast is the name of the table being altered.

ADD id INT FIRST specifies the action to be performed:

ADD indicates that a new column is being added to the table.

id is the name of the new column being added.

INT specifies the data type of the new column, which is an integer type capable of storing whole numbers.

FIRST specifies that the new column should be added as the first column in the table.

This action adds a new column named "id" to the "newbook_mast" table with the default data type of INT, and it will be positioned as the first column in the table.

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.


-- Add a new column named "pub_name" to the table "newbook_mast" with data type VARCHAR(35), positioned after the "pub_id" column
ALTER TABLE newbook_mast ADD pub_name VARCHAR(35) AFTER pub_id,

-- Add a new column named "pub_add" to the table "newbook_mast" with data type VARCHAR(50), positioned after the "dt_of_pub" column
ADD pub_add VARCHAR(50) AFTER dt_of_pub;

Explanation:

This SQL statement alters the structure of the table named "newbook_mast" by adding two new columns at specific positions within the table.

ALTER TABLE is a SQL statement used to modify the structure of a table.

newbook_mast is the name of the table being altered.

The first part of the statement adds a new column named "pub_name" to the table:

ADD pub_name VARCHAR(35) AFTER pub_id specifies the action to be performed.

ADD indicates that a new column is being added to the table.

pub_name is the name of the new column being added.

VARCHAR(35) specifies the data type of the new column, which is a variable-length string with a maximum length of 35 characters.

AFTER pub_id specifies that the new column should be positioned immediately after the existing "pub_id" column in the table.

The second part of the statement adds another new column named "pub_add" to the table:

ADD pub_add VARCHAR(50) AFTER dt_of_pub specifies the action to be performed.

pub_add is the name of the new column being added.

VARCHAR(50) specifies the data type of the new column, which is a variable-length string with a maximum length of 50 characters.

AFTER dt_of_pub specifies that the new column should be positioned immediately after the existing "dt_of_pub" column in the table.

These actions add two new columns named "pub_name" and "pub_add" to the "newbook_mast" table with specific data types and positions within the table schema.

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.


-- Add an index named "cate_id" on the column "cate_id" of the table "newbook_mast"
ALTER TABLE newbook_mast ADD INDEX cate_id(cate_id);

Explanation:

This SQL statement alters the structure of the table named "newbook_mast" by adding an index on a specific column.

ALTER TABLE is a SQL statement used to modify the structure of a table.

newbook_mast is the name of the table being altered.

ADD INDEX specifies the action to be performed, which is adding an index.

cate_id(cate_id) specifies the index to be added:

cate_id is the name of the index being added.

(cate_id) indicates the column(s) on which the index is created. In this case, the index is created on the column "cate_id".

This action adds an index named "cate_id" on the column "cate_id" of the "newbook_mast" table. Indexes are used to improve the performance of queries by allowing for faster data retrieval based on the indexed columns.

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.


-- Add a unique index named "cate_id" on the column "cate_id" of the table "newbook_mast"
ALTER TABLE newbook_mast ADD UNIQUE INDEX cate_id(cate_id);

Explanation:

This SQL statement alters the structure of the table named "newbook_mast" by adding a unique index on a specific column.

ALTER TABLE is a SQL statement used to modify the structure of a table.

newbook_mast is the name of the table being altered.

ADD UNIQUE INDEX specifies the action to be performed, which is adding a unique index.

cate_id(cate_id) specifies the unique index to be added:

cate_id is the name of the index being added.

(cate_id) indicates the column(s) on which the unique index is created. In this case, the index is created on the column "cate_id".

This action adds a unique index named "cate_id" on the column "cate_id" of the "newbook_mast" table. Unique indexes ensure that all values in the indexed column are unique, preventing duplicate values in that column.

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.


-- Add a primary key constraint named "invoice_no" on the column "invoice_no" of the table "tstpurch"
ALTER TABLE tstpurch ADD PRIMARY KEY invoice_no (invoice_no);

Explanation:

This SQL statement alters the structure of the table named "tstpurch" by adding a primary key constraint.

ALTER TABLE is a SQL statement used to modify the structure of a table.

tstpurch is the name of the table being altered.

ADD PRIMARY KEY specifies the action to be performed, which is adding a primary key constraint.

invoice_no is the name of the primary key constraint being added.

(invoice_no) indicates the column(s) on which the primary key constraint is applied. In this case, the primary key constraint is applied on the column "invoice_no".

This action adds a primary key constraint named "invoice_no" on the column "invoice_no" of the "tstpurch" table. Primary keys uniquely identify each record in a table and ensure that each record has a unique identifier.

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.


-- Add a foreign key constraint on the columns "book_id" and "cate_id" of the table "newbook_mast"
-- Referencing the columns "book_id" and "cateid" of the table "torder"
ALTER TABLE newbook_mast
ADD FOREIGN KEY(book_id,cate_id)
REFERENCES torder(book_id,cateid);

Explanation:

This SQL statement alters the structure of the table named "newbook_mast" by adding a foreign key constraint.

ALTER TABLE is a SQL statement used to modify the structure of a table.

newbook_mast is the name of the table being altered.

ADD FOREIGN KEY specifies the action to be performed, which is adding a foreign key constraint.

(book_id,cate_id) indicates the columns on which the foreign key constraint is applied. In this case, the foreign key constraint is applied on the columns "book_id" and "cate_id".

REFERENCES torder(book_id,cateid) specifies the table and columns being referenced by the foreign key constraint:

torder is the name of the referenced table.

(book_id,cateid) indicates the columns in the referenced table "torder" that are being referenced. In this case, the columns "book_id" and "cateid" are referenced.

This action adds a foreign key constraint on the columns "book_id" and "cate_id" of the "newbook_mast" table, referencing the columns "book_id" and "cateid" of the "torder" table. Foreign key constraints ensure referential integrity between related tables, enforcing that values in the foreign key columns of one table match values in the primary or unique key columns of another table.

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.


-- Remove the column "id" from the table "tstpurch"
ALTER TABLE tstpurch DROP id,
-- Add a new column named "id" to the table "tstpurch" with data type INT, marked as NOT NULL, and default value of 0
ADD id INT NOT NULL DEFAULT 0,
-- Add a new column named "chano" to the table "tstpurch" with data type VARCHAR(10), marked as NOT NULL
ADD chano VARCHAR(10) NOT NULL,
-- Add a new column named "chadt" to the table "tstpurch" with data type DATE
ADD chadt DATE,
-- Remove the primary key constraint from the table "tstpurch"
DROP PRIMARY KEY,
-- Add a primary key constraint named "invoice_no" on the column "invoice_no" of the table "tstpurch"
ADD PRIMARY KEY invoice_no (invoice_no),
-- Remove the index named "cate_id" from the table "tstpurch"
DROP INDEX cate_id,
-- Add an index named "cate_id" on the column "cate_id" of the table "tstpurch"
ADD INDEX cate_id(cate_id),
-- Remove the foreign key constraint named "ord_no" from the table "tstpurch"
DROP FOREIGN KEY ord_no,
-- Add a foreign key constraint on the columns "book_id" and "cate_id" of the table "tstpurch"
-- Referencing the columns "book_id" and "cate_id" of the table "torder"
ADD FOREIGN KEY(book_id,cate_id) REFERENCES torder(book_id,cate_id);

Explanation:

This SQL statement alters the structure of the table named "tstpurch" by performing multiple modifications.

ALTER TABLE is a SQL statement used to modify the structure of a table.

Each line performs a specific alteration:

DROP id: Removes the column "id" from the table "tstpurch".

ADD id INT NOT NULL DEFAULT 0: Adds a new column named "id" to the table "tstpurch" with data type INT, marked as NOT NULL, and default value of 0.

ADD chano VARCHAR(10) NOT NULL: Adds a new column named "chano" to the table "tstpurch" with data type VARCHAR(10), marked as NOT NULL.

ADD chadt DATE: Adds a new column named "chadt" to the table "tstpurch" with data type DATE.

DROP PRIMARY KEY: Removes the primary key constraint from the table "tstpurch".

ADD PRIMARY KEY invoice_no (invoice_no): Adds a primary key constraint named "invoice_no" on the column "invoice_no" of the table "tstpurch".

DROP INDEX cate_id: Removes the index named "cate_id" from the table "tstpurch".

ADD INDEX cate_id(cate_id): Adds an index named "cate_id" on the column "cate_id" of the table "tstpurch".

DROP FOREIGN KEY ord_no: Removes the foreign key constraint named "ord_no" from the table "tstpurch".

ADD FOREIGN KEY(book_id,cate_id) REFERENCES torder(book_id,cate_id): Adds a foreign key constraint on the columns "book_id" and "cate_id" of the table "tstpurch", referencing the columns "book_id" and "cate_id" of the table "torder".

These alterations collectively modify the structure of the "tstpurch" table, including adding and removing columns, primary key, indexes, and foreign key constraints.

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