MySQL Tutorial/Table/Alter Table

Add primary key to a table by using the alter table command

   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE myTable(

   ->    ID SMALLINT
   -> );

Query OK, 0 rows affected (0.02 sec) mysql> mysql> desc myTable; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID | smallint(6) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> mysql> ALTER TABLE myTable

   ->    ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL,
   ->    MODIFY ID SMALLINT UNSIGNED NOT NULL,
   ->    ADD PRIMARY KEY (ID);

Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc myTable; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | NO | PRI | | | | Quantity | smallint(5) unsigned | NO | | | | +----------+----------------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.00 sec) mysql></source>

ALTER [IGNORE] TABLE table_name specification [,specification]

Specifications can be:

   <source lang="sql">

ADD [COLUMN] column name (column definitions) [FIRST or AFTER column_name] ADD INDEX [index_name] (column_list) ADD PRIMARY KEY (column_list) ADD UNIQUE [index_name] (column_list) ALTER [COLUMN] column_name {SET DEFAULT default_value or DROP DEFAULT} CHANGE [COLUMN] old_col_name create_definition DROP [COLUMN] col_name DROP PRIMARY KEY DROP INDEX index_name MODIFY [COLUMN] create_definition RENAME [AS] new_tbl_name The IGNORE keyword causes rows with duplicate values in unique keys to be deleted; otherwise, nothing happens. ALTER TABLE employee ADD COLUMN Account_Number INT ALTER TABLE employee ADD INDEX (ID) ALTER TABLE employee ADD PRIMARY KEY (ID) ALTER TABLE employee ADD UNIQUE (ID) ALTER TABLE employee CHANGE ID salary INT ALTER TABLE employee DROP Customer_ID ALTER TABLE employee DROP PRIMARY KEY ALTER TABLE employee DROP INDEX Customer_ID ALTER TABLE employee MODIFY First_Name varchar(100) ALTER TABLE employee RENAME Customer</source>

Alter table to add the foreign key

   <source lang="sql">

mysql> mysql> mysql> mysql> CREATE TABLE Publishers

   -> (
   ->    PubID SMALLINT NOT NULL DEFAULT 1
   -> )
   -> ENGINE=INNODB;

Query OK, 0 rows affected (0.05 sec) mysql> mysql> mysql> CREATE TABLE Books

   -> (
   ->    BookID SMALLINT NOT NULL,
   ->    BookName VARCHAR(40) NOT NULL,
   ->    PubID SMALLINT NOT NULL DEFAULT 1
   -> )
   -> ENGINE=INNODB;

Query OK, 0 rows affected (0.05 sec) mysql> mysql> mysql> ALTER TABLE Books

   -> ADD PRIMARY KEY (BookID),
   -> ADD CONSTRAINT fk_1 FOREIGN KEY (PubID) REFERENCES Publishers (PubID),
   -> ADD COLUMN Format ENUM("paperback", "hardcover") NOT NULL AFTER BookName;

mysql> mysql> desc Books; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | BookID | smallint(6) | NO | | | | | BookName | varchar(40) | NO | | | | | PubID | smallint(6) | NO | | 1 | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> mysql> desc Publishers; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | PubID | smallint(6) | NO | | 1 | | +-------+-------------+------+-----+---------+-------+ 1 row in set (0.02 sec) mysql> mysql> drop table Books; Query OK, 0 rows affected (0.02 sec) mysql> mysql> drop table Publishers; Query OK, 0 rows affected (0.05 sec) mysql></source>

Drop primary key from a table using the alter table command

   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE myTable(

   ->    ID SMALLINT
   -> );

Query OK, 0 rows affected (0.27 sec) mysql> desc myTable; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID | smallint(6) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> mysql> ALTER TABLE myTable

   ->    ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL,
   ->    MODIFY ID SMALLINT UNSIGNED NOT NULL,
   ->    ADD PRIMARY KEY (ID);

Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc myTable; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | NO | PRI | | | | Quantity | smallint(5) unsigned | NO | | | | +----------+----------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> mysql> mysql> ALTER TABLE myTable

   ->    DROP COLUMN Quantity,
   ->    DROP PRIMARY KEY;

Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc myTable; +-------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | NO | | | | +-------+----------------------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.02 sec) mysql></source>

Drop table column with alter table command

   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE myTable(

   ->    ID SMALLINT
   -> );

Query OK, 0 rows affected (0.03 sec) mysql> mysql> desc myTable; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID | smallint(6) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 1 row in set (0.02 sec) mysql> mysql> ALTER TABLE myTable

   ->    ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL,
   ->    MODIFY ID SMALLINT UNSIGNED NOT NULL,
   ->    ADD PRIMARY KEY (ID);

Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc myTable; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | NO | PRI | | | | Quantity | smallint(5) unsigned | NO | | | | +----------+----------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> mysql> ALTER TABLE myTable

   ->    DROP COLUMN Quantity,
   ->    DROP PRIMARY KEY;

Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc myTable; +-------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | NO | | | | +-------+----------------------+------+-----+---------+-------+ 1 row in set (0.02 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.01 sec)</source>

MODIFYing a TABLE

We can change an existing column by using MODIFY in the statement.

   <source lang="sql">

ALTER TABLE MODIFY <column> <new_definition></source>