Tables modification. The ALTER TABLE statement

Before studying this topic, it is recommended that you familiarize yourself with the following topic:

Creating tables. Statement CREATE TABLE. Examples

Search other resources:

1. Modification of tables. The ALTER TABLE statement. Features

The ALTER TABLE statement is intended to modify a previously created table. The operator allows you to perform the following basic operations:

add a new fields to the table;
remove fields from the table;
change the data type of fields;
add and remove restrictions on fields;
other.

The operator has a wide range of options. This topic covers only some of the main ones, namely:

adding a new fields. This is provided by the ADD keyword;
deleting fields. This uses the DROP keyword in combination with the COLUMN keyword;
editing fields. In this case, the combination of words ALTER COLUMN is used.

⇑

2. Change the settings of an existing field. A combination of the ALTER COLUMN keywords

The following simplified syntax is used to change the type of a field.

ALTER TABLE [Table_Name]
    ALTER COLUMN Column_Name New_Type NewRestrictions

where

Table_Name – the name of the table that was created previously;
Column_Name – name of the table field Table_Name, which was created earlier;
New_Type – name of the type that is assigned to the Column_Name field;
New_Restrictions – restrictions imposed on the field. Here you can set new restrictions that were not previously defined.

It is recommended that the table contains no records at the time of the change.

⇑

3. An example of changing the type of a field (column) of a table and setting new restrictions. ALTER TABLE + ALTER COLUMN

In our example, a table is created with fields A, B, C, D. Then, using the ALTER TABLE statement, the type of field C is changed from VARCHAR(20) to DECIMAL. The NOT NULL constraint is also set.

/* Create a table */
CREATE TABLE [MyTable] (
    A INT NOT NULL PRIMARY KEY,
    B FLOAT,
    C VARCHAR(20),
    D DATE
);

/*
Result.
------------------------------------
|  A  |   B   |      C      |   D  |
------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE |
------------------------------------
*/

GO

/* Change the type of column C to DECIMAL and add a NOT NULL constraint */
ALTER TABLE [MyTable]
    ALTER COLUMN C DECIMAL NOT NULL

/*
Result.
---------------------------------
|   A |   B   |   C     |   D   |
---------------------------------
| INT | FLOAT | DECIMAL |  DATE |
---------------------------------
*/

⇑

4. Adding new fields to the table. Keyword ADD

Adding a new field using the ALTER TABLE statement is implemented using the ADD keyword. In this case, the simplified general form of the operator is

ALTER TABLE [Table_Name]
    ADD
        Field1 Type1,
        Field2 Type2,
        ...
        FieldN TypeN

where

Table_Name – the name of created table;
Field1, Field2, FieldN – field names to be added. These names must not repeat existing table names. In other words, field names in a table must be unique;
Type1, Type2, TypeN – field types Field1, Field2, FieldN respectively.

If you need to add one field, then the operator syntax is as follows

ALTER TABLE [Table_Name]
  ADD
    Field Type

where

Field – the name of the field to be added;
Type – the type of field to add.

This case also involves adding field constraints. In this case, one or more restrictions (NOT NULL, NULL, and others) are specified after the Type type.

⇑

5. Example of adding fields to the table

In the example, using the CREATE TABLE command, fields with the following names and their types are formed:

field A of type Int. This field has a NOT NULL and PRIMARY KEY constraint;
field B of type Float.

The following fields are then added to these fields using the ALTER TABLE command with the ADD keyword:

field C of type Varchar(20);
field D of Date type.

/* Create a table named MyTable.
   Add columns A, B
*/

CREATE TABLE [MyTable] (
    A INT NOT NULL PRIMARY KEY,
    B FLOAT,
);

GO

/* Result:
---------------
|  A  |   B   |
---------------
| INT | FLOAT |
---------------
*/

/* Add columns C, D */
ALTER TABLE [MyTable]
    ADD
        C VARCHAR(20),
        D DATE

/* Result:
------------------------------------
|  A  |   B   |      C      |   D  |
------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE |
------------------------------------
*/

⇑

6. Removing fields. Keywords DROP COLUMN

To remove fields from a table, use the ALTER TABLE statement in combination with the DROP COLUMN keywords. In this case, the simplified general form of the operator is as follows

ALTER TABLE [Table_Name]
    DROP COLUMN
        Field1,
        Field2,
        ...
        FieldN

where

Table_Name – the name of the table;
Column1, Column2, ColumnN – the names of the fields (columns) of the table to be deleted. If you set fields that do not exist, an error message will be displayed.

If only one field needs to be removed from the table, the ALTER TABLE statement will look like this

ALTER TABLE [Table_Name]
    DROP COLUMN
        Field

where

Field – the name of the field to be removed.

⇑

7. Example of creating a table, adding a new and deleting an existing field from the table (ALTER TABLE + ADD, ALTER TABLE + DROP COLUMN)

The example demonstrates the use of the following commands:

CREATE TABLE – creating table with 4 fields;
ALTER TABLE with the ADD key – adding a new field to the table;
ALTER TABLE with the DROP COLUMN key – deleting a field from a table.

/* Create a table with the name MyTable.
   Add columns A, B, C, D
*/
CREATE TABLE [MyTable] (
    A INT,
    B FLOAT,
    C VARCHAR(20),
    D DATE
);

GO

/* Result:
------------------------------------
|  A  |   B   |      C      |   D  |
------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE |
------------------------------------
*/

/* Add column E of type CHAR(10) */
ALTER TABLE [MyTable]
    ADD
        E CHAR(10)

GO

/* Result:
-----------------------------------------------
|  A  |   B   |      C      |   D  |     E    |
-----------------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE | CHAR(10) |
-----------------------------------------------
*/

/* Delete column C */
ALTER TABLE [MyTable]
    DROP COLUMN C

/* Result:
---------------------------------
|  A  |   B   |   D  |    E     |
---------------------------------
| INT | FLOAT | DATE | CHAR(10) |
---------------------------------
*/

⇑

8. Removing multiple fields (ALTER TABLE + DROP COLUMN)

The example first creates a table containing 6 fields named A, B, C, D, E, F. Then, using the ALTER TABLE command, two fields named A, C are removed from the table.

/* Create a table named MyTable.
   Add columns A, B, C, D, E, F
*/
CREATE TABLE [MyTable] (
    A INT NOT NULL,
    B FLOAT,
    C VARCHAR(20),
    D DATE,
    E CHAR(10),
    F REAL
);

GO

/* Result:
------------------------------------------------------
|  A  |   B   |      C      |   D  |     E    |   F  |
------------------------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE | CHAR(10) | REAL |
------------------------------------------------------
*/

/* Delete columns C, A */
ALTER TABLE [MyTable]
    DROP COLUMN C, A

/* Result:
----------------------------------
|   B   |   D  |    E     |   F  |
----------------------------------
| FLOAT | DATE | CHAR(10) | REAL |
----------------------------------
*/

⇑

9. An example of changing restrictions in a given table field. The combination ALTER TABLE+ADD+DROP

If you need to add a constraint to an existing field, you can do the following:

remove the field;
create a new field with the same name with the specified constraint. If the data in the table already exists, you must perform operations to copy this data to another table.

The T-SQL code that performs operations to change restrictions is as follows

/* 1. Create a table with the name MyTable.
   Set columns A, B, C, D.
*/
CREATE TABLE [MyTable] (
    A INT NOT NULL PRIMARY KEY,
    B FLOAT,
    C VARCHAR(20),
    D DATE
);

GO

/* Result:
------------------------------------
|  A  |   B   |      C      |   D  |
------------------------------------
| INT | FLOAT | VARCHAR(20) | DATE |
------------------------------------
*/

/* 2. Add a NOT NULL constraint to column B */
/* 2.1. Delete column B */
ALTER TABLE [MyTable]
    DROP COLUMN B

/* 2.2. Add column B with constraint NOT NULL */
ALTER TABLE [MyTable]
    ADD
        B FLOAT NOT NULL

Figure 1 shows a view of the Microsoft SQL Server Management Studio window with the example running.

Figure 1. Operator ALTER TABLE. Adding a NOT NULL constraint to field B

The example shows changing the name of a given field by deleting the existing one (ALTER TABLE + DROP) and adding a new one with a new name (ALTER TABLE + ADD). If a table field contains data, one way to solve the problem is to create a temporary table into which this data will be copied. After that, changes occur in the main table and the temporary table data is copied to this table. This way of solving the problem is given below in order to demonstrate the use of basic operations for working with tables.

/* 1. Create table MyTable */
CREATE TABLE [MyTable] (
    A INT,
    B FLOAT,
    C VARCHAR(20)
);

/*
Result.
-----------------------------
|  A  |   B   |      C      |
-----------------------------
| INT | FLOAT | VARCHAR(20) |
-----------------------------
*/

/* 2. Add arbitrary data to the table MyTable */
INSERT INTO [MyTable] ( A, B, C )
    VALUES ( 1, 5.88, 'First' )

INSERT INTO [MyTable] ( A, B, C )
    VALUES ( 2, 3.5, 'Second' )

INSERT INTO [MyTable] ( A, B, C )
    VALUES ( 4, 8.16, 'Third' )

/*
Result. The MyTable table
-----------------------------
|  A  |   B   |      C      |
-----------------------------
| INT | FLOAT | VARCHAR(20) |
-----------------------------
|  1  |  5.88 |   'First'   |
|  2  |  3.5  |   'Second'  |
|  4  |  8.16 |   'Third'   |
-----------------------------
*/

/* 3. Display MyTable table data for control */
SELECT * FROM MyTable

/*
  Change field name C to ABC in table MyTable
*/

/* 4.1. Create a new copy table in which the C field is replaced with ABC */
CREATE TABLE [Temp] (
    A INT,
    B FLOAT,
    ABC VARCHAR(20)
)

/* 4.2. Copy data from field C to a temporary table */
/* 4.2.1. Copy MyTable.C => Temp.C */
INSERT INTO [Temp] ( A, B, ABC )
    SELECT *
      FROM [MyTable]

/* 4.2.2. Display the data of the Temp temporary table */
SELECT * FROM [Temp]

/* 4.3. Delete field C of table MyTable */
ALTER TABLE [MyTable]
    DROP COLUMN C

/* 4.4. Add field ABC to MyTable */
ALTER TABLE [MyTable]
    ADD ABC VARCHAR(20)

/* 4.5. Delete all previous data from table MyTable */
DELETE FROM [MyTable]

/* 4.6. Copy data from Temp table to MyTable table */
INSERT INTO [MyTable] ( A, B, ABC )
    SELECT *
    FROM [Temp]

/* 4.7. Delete the Temp table */
DROP TABLE [Temp]

/* 5. Display data of MyTable table */
SELECT * FROM [MyTable]

/*
Result. MyTable
-----------------------------
|  A  |   B   |     ABC     |
-----------------------------
| INT | FLOAT | VARCHAR(20) |
-----------------------------
|  1  |  5.88 |   'First'   |
|  2  |  3.5  |   'Second'  |
|  4  |  8.16 |   'Third'   |
-----------------------------
*/

The result of running the program in Microsoft SQL Server Management Studio is shown in Figure 2.

Figure 2. The result of the program execution. Table MyTable with modified column ABC

BestProg

Databases. SQL. Tables modification. The ALTER TABLE statement

Tables modification. The ALTER TABLE statement

Contents

1. Modification of tables. The ALTER TABLE statement. Features

⇑

2. Change the settings of an existing field. A combination of the ALTER COLUMN keywords

⇑

3. An example of changing the type of a field (column) of a table and setting new restrictions. ALTER TABLE + ALTER COLUMN

⇑

4. Adding new fields to the table. Keyword ADD

⇑

5. Example of adding fields to the table

⇑

6. Removing fields. Keywords DROP COLUMN

⇑

7. Example of creating a table, adding a new and deleting an existing field from the table (ALTER TABLE + ADD, ALTER TABLE + DROP COLUMN)

⇑

8. Removing multiple fields (ALTER TABLE + DROP COLUMN)

⇑

9. An example of changing restrictions in a given table field. The combination ALTER TABLE+ADD+DROP

⇑

10. An example of changing the field name. Combination of ALTER TABLE + DROP, ALTER TABLE + ADD statements

⇑

Related topics

⇑

Programming: theory and practice