ALTER TABLE statement in SQL

The ALTER TABLE statement in SQL is used to add, delete, and modify columns in an existing table. This command is also used to add and delete existing constraints on a table.

Syntax ALTER TABLE

Use the ALTER TABLE command to add columns

To add a column for the table, use the following syntax:

 ALTER TABLE ten_bang ADD ten_cot kieu_dulieu; 

Use the ALTER TABLE command to delete the column

To delete a column on a table, use the following syntax:

 ALTER TABLE ten_bang DROP COLUMN ten_cot; 

Use the ALTER TABLE command to change the data type

To change the data type of a column on a table, we use the following syntax:

 ALTER TABLE ten_bang MODIFY COLUMN ten_cot kieu_dulieu; 

Use the ALTER TABLE command to add constraints

To add a NOT NULL constraint to a column on a table we use the following syntax:

 ALTER TABLE ten_bang MODIFY ten_cot kieu_dulieu NOT NULL; 

To add UNIQUE constraint on a table we use the following syntax:

 ALTER TABLE ten_bang 
 ADD CONSTRAINT RangbuocUnique UNIQUE(cot1, cot2.); 

To add CHECK constraints on a table we use the following syntax:

 ALTER TABLE ten_bang 
 ADD CONSTRAINT RangbuocUnique CHECK (DIEUKIEN); 

To add constraints PRIMARY KEY on a table we use the following syntax:

 ALTER TABLE ten_bang 
 ADD CONSTRAINT Khoachinh PRIMARY KEY (cot1, cot2.); 

Use the ALTER TABLE command to delete constraints

 ALTER TABLE ten_bang 
 DROP CONSTRAINT RangbuocUnique; 

If you are using MySQL, the code is:

 ALTER TABLE ten_bang 
 DROP INDEX RangbuocUnique; 

To delete the binding PRIMARY KEY on a table we use the following syntax:

 ALTER TABLE ten_bang 
 DROP CONSTRAINT Khoachinh; 

If you are using MySQL, the code is:

 ALTER TABLE ten_bang 
 DROP PRIMARY KEY; 

Example of using ALTER TABLE in SQL

Suppose the NHANVIEN table has the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

Now we add GIOITINH column to NHANVIEN table .

 ALTER TABLE NHANVIEN ADD GIOITINH char(1); 

Now, the NHANVIEN table has been changed and the following is the result of the SELECT command:

 +----+----------+-----+-----------+----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | GIOITINH | +----+----------+-----+-----------+----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | NULL | | 2 | Loan | 25 | Hanoi | 1500.00 | NULL | | 3 | Nga | 23 | Hanam | 2000.00 | NULL | | 4 | Manh | 25 | Hue | 6500.00 | NULL | | 5 | Huy | 27 | Hatinh | 8500.00 | NULL | | 6 | Cao | 22 | HCM | 4500.00 | NULL | | 7 | Lam | 24 | Hanoi | 10000.00 | NULL | +----+----------+-----+-----------+----------+----------+

Next, when you want to remove the GIOITINH column from the table, we use the following SQL statement:

 ALTER TABLE NHANVIEN DROP GIOITINH; 

Now, the NHANVIEN table has been changed and the following is the result of the SELECT command:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

In the next section, we will take a look at the TRUNCATE TABLE command in SQL, so keep in mind.

Previous article: Index (INDEX) in SQL

Next lesson: TRUNCATE TABLE statement in SQL