MySQL Alter Table

  • ALTER TABLE changes the structure of a table.
  • We can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.
  • We can also change characteristics such as the storage engine used for the table or the table comment.

MySQL Alter Table Syntax:

ALTER TABLE table_name [alter_specification [, alter_specification] ...] [partition_options]

alter specifications are : ADD/DROP COLUMN/CONSTRAINTS, …
Table options are: ENCRYPTIOn=’Y’ or ‘N’,COMPRESSION, MAX_ROWS, …

Examples:

1. Create Table EMP

CREATE TABLE EMP (EMPNO NUMERIC(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMERIC(4), HIREDATE DATETIME, SAL NUMERIC(7, 2), COMM NUMERIC(7, 2), DEPTNO NUMERIC(2));

mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | EMP | | EMPLOYEE | +-------------------+

mysql> describe EMP; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | EMPNO | decimal(4,0) | NO | | NULL | | | ENAME | varchar(10) | YES | | NULL | | | JOB | varchar(9) | YES | | NULL | | | MGR | decimal(4,0) | YES | | NULL | | | HIREDATE | datetime | YES | | NULL | | | SAL | decimal(7,2) | YES | | NULL | | | COMM | decimal(7,2) | YES | | NULL | | | DEPTNO | decimal(2,0) | YES | | NULL | |

select * from EMP;

2. Add column to table EMP

mysql> ALTER TABLE EMP ADD contact_number varchar(15); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe EMP; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | EMPNO | decimal(4,0) | NO | | NULL | | | ENAME | varchar(10) | YES | | NULL | | | JOB | varchar(9) | YES | | NULL | | | MGR | decimal(4,0) | YES | | NULL | | | HIREDATE | datetime | YES | | NULL | | | SAL | decimal(7,2) | YES | | NULL | | | COMM | decimal(7,2) | YES | | NULL | | | DEPTNO | decimal(2,0) | YES | | NULL | | | contact_number | varchar(15) | YES | | NULL | | +----------------+--------------+------+-----+---------+-------+

3. Drop Column from table.

mysql> ALTER TABLE EMP DROP contact_number; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe EMP; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | EMPNO | decimal(4,0) | NO | | NULL | | | ENAME | varchar(10) | YES | | NULL | | | JOB | varchar(9) | YES | | NULL | | | MGR | decimal(4,0) | YES | | NULL | | | HIREDATE | datetime | YES | | NULL | | | SAL | decimal(7,2) | YES | | NULL | | | COMM | decimal(7,2) | YES | | NULL | | | DEPTNO | decimal(2,0) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+

4. Add Constraint to SAL column to above table.

mysql> ALTER TABLE EMP ADD CONSTRAINT salary_condtion -> check (SAL>1000); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0