Export (0) Print
Expand All

ALTER TABLE

SQL Server 2000

Modifies a table definition by altering, adding, or dropping columns and constraints.

Syntax
ALTER TABLE table_name
{ [ ALTER COLUMN column_name 
   {DROP DEFAULT 
   | SET DEFAULT constant_expression 
   | IDENTITY [ ( seed , increment ) ]
   } 
| ADD 
   { < column_definition > | < table_constraint > } [ ,...n ] 
| DROP 
   { [ CONSTRAINT ] constraint_name 
   | COLUMN column }
] }
< column_definition > ::= 
   { column_name data_type } 
   [ [ DEFAULT constant_expression ] 
      | IDENTITY [ ( seed , increment ) ] 
   ] 
   [ROWGUIDCOL]
   [ < column_constraint > ] [ ...n ] ]
< column_constraint > ::= 
   [ NULL | NOT NULL ] 
   [ CONSTRAINT constraint_name ] 
   { 
      | { PRIMARY KEY | UNIQUE } 
      | REFERENCES ref_table [ ( ref_column ) ] 
      [ ON DELETE { CASCADE | NO ACTION } ] 
      [ ON UPDATE { CASCADE | NO ACTION } ] 
   }
< table_constraint > ::= 
   [ CONSTRAINT constraint_name ] 
   { [ { PRIMARY KEY | UNIQUE } 
      { ( column [ ,...n ] ) } 
      | FOREIGN KEY 
      [ ( column [ ,...n ] ) ] 
      REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] 
      [ ON DELETE { CASCADE | NO ACTION } ] 
      [ ON UPDATE { CASCADE | NO ACTION } ] 
   }
Arguments
table_name
Is the name of the new table. Table names must conform to the rules for identifiers. table_name must be unique within the database. table_name can contain a maximum of 128 characters.
ALTER COLUMN
Specifies that the given column is to be changed or altered.
column_name
Is the name of a column in the table. Column names must conform to the rules for identifiers and must be unique in the table.
data_type
Specifies the column data type. For information about data types, see Data Types.
DEFAULT
Specifies the value provided for the column when a value is not explicitly supplied during an insert action. DEFAULT definitions can be applied to any column except those defined by the IDENTITY property. DEFAULT definitions are removed when the table is dropped. Only a constant value, such as a character string or a date function, can be used as a default.
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE) provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned only to int columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment, or neither. If neither is specified, the default is (1,1).
seed
Is the value used for the first row that is loaded into the table.
increment
Is the incremental value added to the identity value of the previous row that is loaded.
ADD
Specifies that one or more column definitions, computed column definitions, or table constraints are added.
DROP { [CONSTRAINT] constraint_name | COLUMN column}
Specifies that constraint_name or column_name is to be removed from the table.
ROWGUIDCOL
Indicates that the new column is a row global unique identifier column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

ROWGUIDCOL automatically generates values for new rows inserted into the table.

NULL | NOT NULL
Are keywords that specify whether null values are allowed in the column. NULL is not strictly a constraint but can be specified in the same manner as NOT NULL.
CONSTRAINT
Is an optional keyword indicating the beginning of a definition for a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint. Constraints are special properties that enforce data integrity and create special types of indexes for the table and its columns.
constraint_name
Is the name of a constraint. constraint_name is optional and must be unique within a database. If constraint_name is not specified, SQL Server CE generates a constraint name.
PRIMARY KEY
Is a constraint that enforces entity integrity for a given column or columns using a unique index. Only one PRIMARY KEY constraint can be created per table.
UNIQUE
Is a constraint that provides entity integrity for a given column or columns using a unique index. Columns in a UNIQUE constraint must also be NOT NULL. A table can have multiple UNIQUE constraints.

Note  SQL Server CE can use indexes to enforce PRIMARY KEY and UNIQUE constraints. You should not rely on this behavior nor try to manipulate any indexes that are created as part of a constraint.

FOREIGN KEY...REFERENCES
Is a constraint that provides referential integrity for the data in the column. FOREIGN KEY constraints require that each value in the column exists in the specified column in the referenced table.
ref_table
Is the name of the table referenced by the FOREIGN KEY constraint.
( ref_column [ ,...n ] )
Is a column or list of columns from the table referenced by the FOREIGN KEY constraint.
ON DELETE {CASCADE | NO ACTION}
Specifies what action takes place to a row in the table that is created when that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.
If CASCADE is specified, a row is deleted from the referencing table when the corresponding referenced row is deleted from the parent table. If NO ACTION is specified, SQL Server CE raises an error, and the delete action on the referenced row in the parent table is rolled back.
For example, in the Northwind database, the Orders table has a referential relationship with the Customers table: the Orders.CustomerID foreign key references the Customers.CustomerID primary key.
If a DELETE statement is executed on a row in the Customers table and an ON DELETE CASCADE action is specified for Orders.CustomerID, SQL Server CE checks for one or more dependent rows in the Orders table. If any exist, the dependent rows in the Orders table are deleted, as well as the row referenced in the Customers table.
Alternately, if NO ACTION is specified, SQL Server CE raises an error and rolls back the delete action on the row in the Customers table when there is at least one row in the Orders table that references it.
ON UPDATE {CASCADE | NO ACTION}
Specifies what action takes place to a row in the table created, when that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.
If CASCADE is specified, the row is updated in the referencing table if the corresponding referenced row is updated in the parent table. If NO ACTION is specified, SQL Server CE raises an error, and the update action on the referenced row in the parent table is rolled back.
For example, in the Northwind database, the Orders table has a referential relationship with the Customers table: the Orders.CustomerID foreign key references the Customers.CustomerID primary key.
If an UPDATE statement is executed on a row in the Customers table and an ON UPDATE CASCADE action is specified for Orders.CustomerID, SQL Server CE checks for one or more dependent rows in the Orders table. If any exist, the dependent rows in the Orders table are updated, as well as the row referenced in the Customers table.
Alternately, if NO ACTION is specified, SQL Server CE raises an error and rolls back the update action on the referenced row in the Customers table when there is at least one row in the Orders table that references it.
column
Is a column or list of columns, in parentheses, used in table constraints to indicate the columns used in the definition of the constraint.
n
Is a placeholder indicating that the preceding item can be repeated n number of times.
Examples
A. Changing the seed and increment values on the identity column

The following example changes the seed and increment values on the identity column.

CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))
INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)

B. Adding a default value to the identity column

The following example modifies the CompanyName column so that it has a default value.

ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'

C. Dropping a default value from the identity column

The following example modifies the CompanyName column so that it does not have a default value.

ALTER TABLE MyCustomers ALTER COLUMN CompanyName DROP DEFAULT

Show:
© 2014 Microsoft