×
☰ See All Chapters

MySQL ALTER TABLE - Changing the structure of the database tables

ALTER TABLE statement is used to change the structure of the database tables like to add, modify, drop or delete columns in a table. ALTER statement can also be used to rename the table. Using ALTER statement we can update below listed structure of a table.

  1. Add columns to table 

  2. Delete columns from table 

  3. Modifying data type of a column 

  4. Modifying length, precision, or scale of a column 

  5. Modifying column constraint 

  6. Rename table 

Adding columns to table in MySQL

One of the basic rules for adding columns to an existing table is that the column you are adding cannot be defined as NOT NULL if data currently exists in the table. If necessary to add column with NOT NULL constraint then do the following

  • Add the column and define it as NULL. (The column does not have to contain a value.)  

  • Insert a value into the new column for every row of data in the table. 

  • Alter the table to change the column’s attribute to NOT NULL. 

Syntax:

ALTER TABLE <table-name>

ADD  <column-name> <data-type> <constraint>;

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT NOT NULL,

EMAIL VARCHAR(30) UNIQUE,

CONSTRAINT CUSTOMER_UQ

UNIQUE (CUSTOMER_ID, EMAIL)

);

 

ALTER TABLE CUSTOMER

ADD PHONE NUMERIC (10);

Adding Auto-Incrementing Columns to a Table in MySQL

Sometimes it is necessary to create a column that auto-increments itself to give a unique sequence number for a particular row. You could do this for many reasons, such as not having a natural key for the data, or wanting to use a unique sequence number to sort the data.

In MySQL, the implementation provides the SERIAL method to produce a truly unique value for the table.

Example to add Auto-Incrementing Columns to a Table in MySQL

CREATE TABLE TEST_INCREMENT(

ID SERIAL,

TEST_NAME VARCHAR(20));

 

INSERT INTO TEST_INCREMENT (TEST_NAME)

VALUES ('FRED'),('JOE'),('MIKE'),('TED');

mysql-alter-table-0

Deleting columns from table in MySQL

Syntax:

ALTER TABLE <table-name>

DROP <column-name>;

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT NOT NULL,

PHONE  NUMERIC (10)  NULL,

EMAIL VARCHAR(30) UNIQUE,

CONSTRAINT CUSTOMER_UQ

UNIQUE (CUSTOMER_ID,PHONE,EMAIL)

);

 

ALTER TABLE CUSTOMER

DROP PHONE;

Modifying data type of a column in MySQL

Syntax:

ALTER TABLE <table-name>

MODIFY <column-name> <new-data-type>;

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT,

PHONE  NUMERIC(10) NOT NULL

);

ALTER TABLE CUSTOMER

MODIFY PHONE VARCHAR(20);

Modifying length, precision, or scale of a column  in MySQL

Syntax:

ALTER TABLE <table-name>

MODIFY <column-name> <data-type>(new-length);

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT,

PHONE  VARCHAR(20) NOT NULL

);

ALTER TABLE CUSTOMER

MODIFY PHONE VARCHAR(30);

Modifying column constraint in MySQL

Syntax:

ALTER TABLE <table-name>

MODIFY <column-name> <data-type> <new-constraint>;

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT,

PHONE  NUMERIC(10) NOT NULL

);

ALTER TABLE CUSTOMER

MODIFY PHONE NUMERIC(10) NULL;

 

 

 

 

All Chapters
Author