SQL ALTER TABLE
Summary: This tutorial shows you how to use the SQL ALTER TABLE
to change the structure of existing tables in the database.
Once you create a new table, you may want to change its structure because business requirements change. To modify the structure of a table, you use the ALTER TABLE
statement. The ALTER TABLE
the statement allows you to perform the following operations on an existing table:
- Add a new column using the
ADD
clause. - Modify attribute of a column such as a constraint, default value, etc. using the
MODIFY
clause. - Remove columns using the
DROP
clause.
We will examine each operation in detail in the following sections.
SQL ALTER TABLE ADD column
The following statement illustrates the ALTER TABLE
with the ADD
the clause that allows you to add one or more columns to a table.
ALTER TABLE table_name
ADD new_colum data_type column_constraint [AFTER existing_column];
To add one or more columns to a table, you need to perform the following steps:
- First, specify the table that you want to add column denoted by the
table_name
after theALTER TABLE
clause. - Second, place the new column definition after the
ADD
clause. If you want to specify the order of the new column in the table, you can use the optional clauseAFTER existing_column
.
Note that if you omit the AFTER
clause, all the new columns will be added after the last column of the table.
Let’s look at some examples of adding new columns to the courses
the table that we created in the create table tutorial
The following statement adds a new column named credit_hours
to the courses
table.
ALTER TABLE courses ADD credit_hours INT NOT NULL;
You can add multiple columns to a table using a single ALTER TABLE
statement. For example, The following statement adds the fee
and max_limit
columns to the courses
table and places these columns after the course_name
column.
ALTER TABLE courses
ADD fee NUMERIC (10, 2) AFTER course_name,
ADD max_limit INT AFTER course_name;
SQL ALTER TABLE MODIFY column
The MODIFY
the clause allows you to change some attributes of the existing column e.g., NOT NULL
,UNIQUE
, and data type.
The following statement shows you the syntax of the ALTER TABLE
statement with the DROP
clause.
ALTER TABLE table_name
MODIFY column_definition;
Notice that you should modify the attributes of columns of a table that has no data. Because changing the attributes of a column in a table that already has data may result in permanent data loss.
For example, if the data type of the column is VARCHAR,
and you change it to INT
, the database system has to convert the data from VARCHAR
to INT.
If the conversion fails, the database system may use the default value of the column, which may not be what you expected.
The following ALTER TABLE MODIFY
statement changes the attribute of the fee
column to NOT NULL.
ALTER TABLE courses
MODIFY fee NUMERIC (10, 2) NOT NULL;
SQL ALTER TABLE DROP columns
When a column of a table is obsolete and not used by any other database objects such as triggers, views, stored and stored procedures, you need to remove it from the table.
To remove one or more columns, you use the following syntax:
ALTER TABLE table_name
DROP column_name,
DROP colum_name,
...
For example, to remove the fee
column of the courses
table, you use the following statement.
ALTER TABLE courses DROP COLUMN fee;
To remove more than one column at the same time, you use multiple DROP COLUMN
clauses separated by a comma (,).
For example, the following statement removes the max_limit
and credit_hours
of the courses
table.
ALTER TABLE courses
DROP COLUMN max_limit,
DROP COLUMN credit_hours;
In this tutorial, you have learned step by step how to use the SQL ALTER TABLE
statement to add, modify, and drop one or more columns in a table.
0 Comments
CAN FEEDBACK
Emoji