ALTER TABLE Statement in SQL

This guide covers alter table statement in SQL with practical context and easy-to-follow details. Use it to understand the subject and apply the information confidently.

Syntax ALTER TABLE

How to 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;

How to 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;

How to 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;

How to 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.);

How to 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