ALTER TABLE

Syntax:: ALTER TABLE table
{
    ADD
    {
       column_name data_type [ ( size ) ]
       [ DEFAULT value ]
       { [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } ] }
       { [ CONSTRAINT constraint_name ] }
    }
    |
    DROP { COLUMN column_name | [ CONSTRAINT ] constraint_name }
}
table: Specifies which table is to be altered.
column_name: The name of the column being added, altered, or dropped.
data_type: The data type of the column being added or altered.
size: Is the length of the data that can be entered into a field.
DEFAULT value: Is the default value for the column being altered.
NULL | NOT NULL: Is a parameter that indicates whether a column can or cannot contain null values.
PRIMARY KEY: Is a parameter that identifies the column or set of columns whose values uniquely identify each row in a table. Each table can only have one primary key constraint.
UNIQUE: Is a constraint that enforces the uniqueness of the values in a set of columns.
constraint_name: The name of the constraint to be added or dropped.
precision: Specifies the precision for the data type.
scale: Specifies the scale for the data type.

The ALTER TABLE statement can be used to modify an existing table by adding, altering, or dropping columns and indexes.

Examples

Code:


				ALTER TABLE Sales 

				ADD UnitPrice MONEY;

Output:


				The command(s) completed successfully.

Explanation:

This example adds a single field to the 'Sales' table by specifying the new field name and data type.

Language(s): MS SQL Server

Code:


				ALTER TABLE Sales 

				ADD UnitPrice MONEY 

				DEFAULT 3.00 NOT NULL;

Output:


				(3 row(s) affected)

Explanation:

This example adds the reserved words NOT NULL to require valid data to be added to that field. However, you must also specify a default value for any data already inserted in the table.

Language(s): MS SQL Server

Code:


				ALTER TABLE Sales
 
				DROP COLUMN UnitPrice;

Output:


				The command(s) completed successfully.

Explanation:

This example demonstrates how to use DROP COLUMN to delete a single field.

Language(s): MS SQL Server

Code:


				ALTER TABLE Sales 

				ADD Item VARCHAR (30) 

				CONSTRAINT UniqueConstraint UNIQUE;

Output:


				The command(s) completed successfully.

Explanation:

This example defines an index for a new field by using the CONSTRAINT clause. Indexes cannot be created for the following data types: text, ntext, or image data type.

Note: The TEXT and INT data types cannot have a field size property set.

Language(s): MS SQL Server

Code:


				ALTER TABLE Names 

				ADD CONSTRAINT UniqueValues 

				UNIQUE (FirstName, LastName);

Output:


				The command(s) completed successfully.

Explanation:

This shows how ADD CONSTRAINT can be used to add a multi-field index.

Language(s): MS SQL Server

Code:


				ALTER TABLE Names

				 DROP CONSTRAINT UniqueValues;

Output:


				The command(s) completed successfully.

Explanation:

DROP CONSTRAINT can also be used to remove a multi-field index.

Primary links

JetSQL » Statements » ALTER TABLE

Examples

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

See Also: