Altering a Table - ALTER TABLE statement

The ALTER TABLE statement modifies the schema of a table. The Transact-SQL language allows the following types of alteration:

Add or drop one or more new columns
Modify column properties
Add or remove integrity constraints
Enable or disable constraints
Rename tables and other database objects

The following sections describe these types of changes.

Adding or Dropping a New Column

You can use the ADD clause of the ALTER TABLE statement to add a new column to the existing table. Only one column can be added for each ALTER TABLE statement. Example 5.21 shows the use of the ADD clause.

Example 5.21

USE sample;
ALTER TABLE employee
	ADD telephone_no CHAR(12) NULL;Code language: PHP (php)

The ALTER TABLE statement in Example 5.21 adds the column telephone_no to the employee table. The Database Engine populates the new column either with NULL or IDENTITY values or with the specified default. For this reason, the new column must either be nullable or have a default constraint.

Note – There is no way to insert a new column in a particular position in the table. The column, which is added using the ADD clause, is always inserted at the end of the table.

The DROP COLUMN clause provides the ability to drop an existing column of the table, as shown in Example 5.22.

Example 5.22

USE sample;
ALTER TABLE employee
	DROP COLUMN telephone_no;Code language: PHP (php)

The ALTER TABLE statement in Example 5.22 removes the telephone_no column, which was added to the employee table with the ALTER TABLE statement in Example 5.21.

Modifying Column Properties

The Transact-SQL language supports the ALTER COLUMN clause of ALTER TABLE to modify properties of an existing column. The following column properties can be modified:

Data type
Nullability

Example 5.23 shows the use of the ALTER COLUMN clause.

Example 5.23

USE sample;
ALTER TABLE department
	ALTER COLUMN location CHAR(25) NOT NULL;Code language: PHP (php)

The ALTER TABLE statement in Example 5.23 changes the previous properties (CHAR(30), nullable) of the location column of the department table to new properties (CHAR(25), not nullable).

Adding or Removing Integrity Constraints

A new integrity constraint can be added to a table using the ALTER TABLE statement and its option called ADD CONSTRAINT. Example 5.24 shows how you can use the ADD CONSTRAINT clause in relation to a check constraint.

Example 5.24

USE sample;
CREATE TABLE sales
	(order_no INTEGER NOT NULL,
	order_date DATE NOT NULL,
	ship_date DATE NOT NULL);
ALTER TABLE sales
	ADD CONSTRAINT order_check CHECK(order_date <= ship_date);Code language: PHP (php)

The CREATE TABLE statement in Example 5.24 creates the sales table with two columns of the DATE data type: order_date and ship_date. The subsequent ALTER TABLE statement defines an integrity constraint named order_check, which compares both of the values and displays an error message if the shipping date is earlier than the order date.

Example 5.25 shows how you can use the ALTER TABLE statement to additionally define the primary key of a table.

Example 5.25

USE sample;
ALTER TABLE sales
	ADD CONSTRAINT primaryk_sales PRIMARY KEY(order_no);Code language: PHP (php)

The ALTER TABLE statement in Example 5.25 declares the primary key for the sales table.

Each integrity constraint can be removed using the DROP CONSTRAINT clause of the ALTER TABLE statement, as shown in Example 5.26.

Example 5.26

USE sample;
ALTER TABLE sales
DROP CONSTRAINT order_check;Code language: PHP (php)

The ALTER TABLE statement in Example 5.26 removes the CHECK constraint called order_check, specified in Example 5.24.

Note – You cannot use the ALTER TABLE statement to modify a definition of an integrity constraint. In this case, the constraint must be re-created—that is, dropped and then added with the new definition.

Enabling or Disabling Constraints

As previously stated, an integrity constraint always has a name that can be explicitly declared using the CONSTRAINT option or implicitly declared by the system. The name of all (implicitly or explicitly) declared constraints for a table can be viewed using the system procedure sp_helpconstraint.

A constraint is enforced by default during future insert and update operations. Additionally, the existing values in the column(s) are checked against the constraint. Otherwise, a constraint that is created with the WITH NOCHECK option is disabled in the second case. In other words, if you use the WITH NOCHECK option, the constraint will be applied only to future insert and update operations. (Both options, WITH CHECK and WITH NOCHECK, can be applied only with the CHECK and FOREIGN KEY constraints.)

Example 5.27 shows how you can disable all existing constraints for a table.

Example 5.27

USE sample;
ALTER TABLE sales
	NOCHECK CONSTRAINT ALL;Code language: PHP (php)

In Example 5.27, the keyword ALL is used to disable all the constraints on the sales table.

Note – Use of the NOCHECK option is not recommended. Any constraint violations that are suppressed may cause future updates to fail.

Renaming Tables and Other Database Objects

The sp_rename system procedure modifies the name of an existing table (and any other existing database objects, such as databases, views, or stored procedures). Examples 5.28 and 5.29 show the use of this system procedure.

Example 5.28

USE sample;
EXEC sp_rename @objname = department, @newname = subdivisionCode language: PHP (php)

Example 5.28 renames the department table to subdivision.

Example 5.29

USE sample;
EXEC sp_rename @objname = 'sales.order_no' , @newname = ordernumberCode language: PHP (php)

Example 5.29 renames the order_no column in the sales table. If the object to be renamed is a column in a table, the specification must be in the form table_name. column_name.

Note – Do not use the sp_rename system procedure, because changing object names can influence other database objects that reference them. Drop the object and re-create it with the new name.

Removing Database Objects

All Transact-SQL statements that are used to remove a database object have the following general form:

DROP object_type object_name

Each CREATE object statement has the corresponding DROP object statement. The statement

DROP DATABASE database1 {, ...}

removes one or more databases. This means that all traces of the database are removed from your database system. One or more tables can be removed from a database with the following statement:

DROP TABLE table_name1 {, ...}

All data, indices, and triggers belonging to the removed table are also dropped. (In contrast, all views that are defined using the dropped table are not removed.) Only the user with the corresponding privileges can remove a table.

In addition to DATABASE and TABLE, objects in the DROP statement can be, among others, the following:

TYPE
SYNONYM
PROCEDURE
INDEX
VIEW
TRIGGER
SCHEMA

The statements DROP TYPE and DROP SYNONYM drop a type and a synonym, respectively. The rest of the statements are described in different chapters.