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

ALTER TABLE learn SQL

Jessica Tanner

Update 25 May 2019

You should read it

May be interested

How to Write an Artist's Statement
it is quite hard to write a really good artist statement, even if you're a good writer. the statement accompanies a piece or set of work you've painted, drawn, photographed, created, and it must sum up such elements as motivation,...
INSERT statement in SQL Server
the insert statement in sql server (transact-sql) is used to insert one or more records into a table.
35 tools, scripts and plugins to build HTML Table
there are many ways to visualize data, ie you can design a beautiful infographics or create interactive charts. it all depends on your data and how you want to present them.
RIGHT JOIN in SQL
right join in sql is a type of join that returns all records from the right side table and matching records from the left side table.
Table fans and box fans, which is the smart choice?
electric fans are indispensable household appliances, especially on hot sunny days. in particular, box fans and table fans are cooling devices are quite a lot of users interested. however, the selection of box fans and table fans also makes many people feel puzzled by the similarities of utility and price.
Should a table drill be used?
table drilling machines provide the most accurate drilling holes, meeting the demand for industrial and continuous drilling on wood and metal.
Table operations in Word
introduce table operations in word. table operations in word 2013 include: 1. draw cells in a table. - creating a table can not ignore merge cell operation, for example, if you want the column of the school day to have 2 values of even and odd dates, make 2 columns into one.
How to fix table errors in Word overflow
error word overflow table can be corrected in 2 different ways and very simple to perform, helping to bring the table in word aligned correctly.
The TRUNCATE TABLE command in SQL Server
the truncate table statement is used to delete all records from a table in sql server.
Instructions for naming Excel tables
tables in excel are named table 1, table2, table 3,... but if you keep the names like this, it will be difficult to handle the table. therefore, users should change and name the excel table.