quote:

---

Transact-SQL Reference


CREATE TABLE
Creates a new table.

Syntax
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]

< column_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}

< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}

Arguments
database_name

Is the name of the database in which the table is created. database_name must specify the name of an existing database. database_name defaults to the current database if not specified. The login for the current connection must be associated with an existing user ID in the database specified by database_name, and that user ID must have create table permissions.

owner

Is the name of the user ID that owns the new table. owner must be an existing user ID in the database specified by database_name. owner defaults to the user ID associated with the login for the current connection in the database specified in database_name. If the CREATE TABLE statement is executed by a member of the sysadmin fixed server role, or a member of the db_dbowner or db_ddladmin fixed database roles in the database specified by database_name, owner can specify a user ID other than the one associated with the login of the current connection. If the CREATE TABLE statement is executed by a login associated with a user ID that has only create table permissions, owner must specify the user ID associated with the current login. Members of the sysadmin fixed server role, or logins aliased to the dbo user are associated with the user ID dbo; therefore, tables created by these users default to having dbo as the owner. Tables created by any logins not in either of these two roles have owner default to the user ID associated with the login.

table_name

Is the name of the new table. Table names must conform to the rules for identifiers. The combination of owner.table_name must be unique within the database. table_name can contain a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

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. column_name can be omitted for columns created with a timestamp data type. The name of a timestamp column defaults to timestamp if column_name is not specified.

computed_column_expression

Is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.


Note Each row in a table can have different values for columns involved in a computed column, therefore the computed column may not have the same value for each row.

The nullability of computed columns is determined automatically by SQL Server based on the expressions used. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will produce NULL results as well. Use the COLUMNPROPERTY function (AllowsNull property) to investigate the nullability of any computed column in a table. An expression expr that is nullable can be turned into a non-nullable one by specifying ISNULL(check_expression, constant) where the constant is a non-NULL value substituted for any NULL result.

ON {filegroup | DEFAULT}

Specifies the filegroup on which the table is stored. If filegroup is specified, the table is stored in the named filegroup. The filegroup must exist within the database. If DEFAULT is specified, or if ON is not specified at all, the table is stored on the default filegroup.

ON {filegroup | DEFAULT} can also be specified in a PRIMARY KEY or UNIQUE constraint. These constraints create indexes. If filegroup is specified, the index is stored in the named filegroup. If DEFAULT is specified, the index is stored in the default filegroup. If no filegroup is specified in a constraint, the index is stored on the same filegroup as the table. If the PRIMARY KEY or UNIQUE constraint creates a clustered index, the data pages for the table are stored in the same filegroup as the index.



Note DEFAULT, in the context of ON {filegroup | DEFAULT} and TEXTIMAGE_ON {filegroup | DEFAULT}, is not a keyword. DEFAULT is an identifier for the default filegroup and must be delimited, as in ON "DEFAULT" or ON [DEFAULT] and TEXTIMAGE_ON "DEFAULT" or TEXTIMAGE_ON [DEFAULT].


TEXTIMAGE_ON

Are keywords indicating that the text, ntext, and image columns are stored on the specified filegroup. TEXTIMAGE ON is not allowed if there are no text, ntext, or image columns in the table. If TEXTIMAGE_ON is not specified, the text, ntext, and image columns are stored in the same filegroup as the table.

data_type

Specifies the column data type. System or user-defined data types are acceptable. User-defined data types are created with sp_addtype before they can be used in a table definition.

The NULL/NOT NULL assignment for a user-defined data type can be overridden during the CREATE TABLE statement. However, the length specification cannot be changed; you cannot specify a length for a user-defined data type in a CREATE TABLE statement.

DEFAULT

Specifies the value provided for the column when a value is not explicitly supplied during an insert. DEFAULT definitions can be applied to any columns except those defined as timestamp, or those with the IDENTITY property. DEFAULT definitions are removed when the table is dropped. Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

constant_expression

Is a constant, NULL, or a system function used as the default value for the column.

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ 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 to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) 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 very first row loaded into the table.

increment

Is the incremental value added to the identity value of the previous row loaded.

NOT FOR REPLICATION

Indicates that the IDENTITY property should not be enforced when a replication login such as sqlrepl inserts data into the table. Replicated rows must retain the key values assigned in the publishing database; the NOT FOR REPLICATION clause ensures that rows inserted by a replication process are not assigned new identity values. Rows inserted by other logins continue to have new identity values created in the usual way. It is recommended that a CHECK constraint with NOT FOR REPLICATION also be defined to ensure that the identity values assigned are within the range wanted for the current database.

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. The ROWGUIDCOL keyword is not valid if the database compatibility level is 65 or lower. For more information, see sp_dbcmptlevel.

The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. It also does not automatically generate values for new rows inserted into the table. To generate unique values for each column, either use the NEWID function on INSERT statements or use the NEWID function as the default for the column.

collation_name

Specifies the collation for the column. Collation name can be either a Windows collation name or a SQL collation name. The collation_name is applicable only for columns of the char, varchar, text, nchar, nvarchar, and ntext data types. If not specified, the column is assigned either the collation of the user-defined data type, if the column is of a user-defined data type, or the default collation of the database.

For more information about the Windows and SQL collation names, see COLLATE.

CONSTRAINT

Is an optional keyword indicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint definition. Constraints are special properties that enforce data integrity and they may create indexes for the table and its columns.

constraint_name

Is the name of a constraint. Constraint names must be unique within a database.

NULL | NOT NULL

Are keywords that determine if null values are allowed in the column. NULL is not strictly a constraint but can be specified in the same manner as NOT NULL.

PRIMARY KEY

Is a constraint that enforces entity integrity for a given column or columns through 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 through a unique index. A table can have multiple UNIQUE constraints.

CLUSTERED | NONCLUSTERED

Are keywords to indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED and UNIQUE constraints default to NONCLUSTERED.

You can specify CLUSTERED for only one constraint in a CREATE TABLE statement. If you specify CLUSTERED for a UNIQUE constraint and also specify a PRIMARY KEY constraint, the PRIMARY KEY defaults to NONCLUSTERED.

[WITH FILLFACTOR = fillfactor]

Specifies how full SQL Server should make each index page used to store the index data. User-specified fillfactor values can be from 1 through 100, with a default of 0. A lower fill factor creates the index with more space available for new index entries without having to allocate new space.

FOREIGN KEY...REFERENCES

Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on 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 created, if 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 if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the 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 checks for one or more dependent rows in the Orders table. If any, the dependent rows in the Orders table are deleted, as well as the row referenced in the Customers table.

On the other hand, if NO ACTION is specified, SQL Server raises an error and rolls back the delete action on the Customers row if 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, if 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 that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the 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: 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 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.

Alternately, if NO ACTION is specified, SQL Server raises an error and rolls back the update action on the Customers row if there is at least one row in the Orders table that references it.

CHECK

Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

NOT FOR REPLICATION

Keywords used to prevent the CHECK constraint from being enforced during the distribution process used by replication. When tables are subscribers to a replication publication, do not update the subscription table directly, instead update the publishing table, and let replication distribute the data back to the subscribing table. A CHECK constraint can be defined on the subscription table to prevent users from modifying it. Unless the NOT FOR REPLICATION clause is added, however, the CHECK constraint also prevents the replication process from distributing modifications from the publishing table to the subscribing table. The NOT FOR REPLICATION clause means the constraint is enforced on user modifications but not on the replication process.

The NOT FOR REPLICATION CHECK constraint is applied to both the before and after image of an updated record to prevent records from being added to or deleted from the replicated range. All deletes and inserts are checked; if they fall within the replicated range, they are rejected.

When this constraint is used with an identity column, SQL Server allows the table not to have its identity column values reseeded when a replication user updates the identity column.

logical_expression

Is a logical expression that returns TRUE or FALSE.

column

Is a column or list of columns, in parentheses, used in table constraints to indicate the columns used in the constraint definition.

[ASC | DESC]

Specifies the order in which the column or columns participating in table constraints are sorted. The default is ASC.

n

Is a placeholder indicating that the preceding item can be repeated n number of times.

Remarks
SQL Server can have as many as two billion tables per database and 1,024 columns per table. The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. If you create tables with varchar, nvarchar, or varbinary columns in which the total defined width exceeds 8,060 bytes, the table is created, but a warning message appears. Trying to insert more than 8,060 bytes into such a row or to update a row so that its total row size exceeds 8,060 produces an error message and the statement fails.

CREATE TABLE statements that include a sql_variant column can generate the following warning:

The total row size (xx) for table 'yy' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
This warning occurs because sql_variant can have a maximum length of 8016 bytes. When a sql_variant column contains values close to the maximum length, it can overshoot the row's maximum size limit.

Each table can contain a maximum of 249 nonclustered indexes and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.

SQL Server does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.

Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.


All other local temporary tables are dropped automatically at the end of the current session.


Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
A local temporary table created within a stored procedure or trigger is distinct from a temporary table with the same name created before the stored procedure or trigger is called. If a query references a temporary table, and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example:

CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO

Here is the result set:

(1 row(s) affected)

Test1Col
-----------
1

(1 row(s) affected)

Test2Col
-----------
2

When you create local or global temporary tables, the CREATE TABLE syntax supports constraint definitions with the exception of FOREIGN KEY constraints. If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message indicating that the constraint was skipped, and the table is still created without the FOREIGN KEY constraints. Temporary tables cannot be referenced in FOREIGN KEY constraints.

Consider using table variables instead of temporary tables. Temporary tables are useful in cases when indexes need to be created explicitly on them, or when the table values need to be visible across multiple stored procedures or functions. In general, table variables contribute to more efficient query processing. For more information, see table.

PRIMARY KEY Constraints
A table can contain only one PRIMARY KEY constraint.


The index generated by a PRIMARY KEY constraint cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.


If CLUSTERED or NONCLUSTERED is not specified for a PRIMARY KEY constraint, CLUSTERED is used if there are no clustered indexes specified for UNIQUE constraints.


All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.
UNIQUE Constraints
If CLUSTERED or NONCLUSTERED is not specified for a UNIQUE constraint, NONCLUSTERED is used by default.


Each UNIQUE constraint generates an index. The number of UNIQUE constraints cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.
FOREIGN KEY Constraints
When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.


FOREIGN KEY constraints are applied to the preceding column unless source columns are specified.


FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER.


FOREIGN KEY constraints can reference another column in the same table (a self-reference).


The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.


The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.


CASCADE may not be specified if a column of type timestamp is part of either the foreign key or the referenced key.


It is possible to combine CASCADE and NO ACTION on tables that have referential relationships with each other. If SQL Server encounters NO ACTION, it terminates and rolls back related CASCADE actions. When a DELETE statement causes a combination of CASCADE and NO ACTION actions, all the CASCADE actions are applied before SQL Server checks for any NO ACTION.


A table can contain a maximum of 253 FOREIGN KEY constraints.


FOREIGN KEY constraints are not enforced on temporary tables.


A table can reference a maximum of 253 different tables in its FOREIGN KEY constraints.


FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.
DEFAULT Definitions
A column can have only one DEFAULT definition.


A DEFAULT definition can contain constant values, functions, SQL-92 niladic functions, or NULL. The table shows the niladic functions and the values they return for the default during an INSERT statement. SQL-92 niladic function Value returned
CURRENT_TIMESTAMP Current date and time.
CURRENT_USER Name of user performing insert.
SESSION_USER Name of user performing insert.
SYSTEM_USER Name of user performing insert.
USER Name of user performing insert.


constant_expression in a DEFAULT definition cannot refer to another column in the table, or to other tables, views, or stored procedures.


DEFAULT definitions cannot be created on columns with a timestamp data type or columns with an IDENTITY property.


DEFAULT definitions cannot be created for columns with user-defined data types if the user-defined data type is bound to a default object.
CHECK Constraints
A column can have any number of CHECK constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple CHECK constraints for a column are validated in the order created.


The search condition must evaluate to a Boolean expression and cannot reference another table.


A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.
CHECK CONSTRAINTS and rules serve the same function of validating the data during INSERT and DELETE statements.

When a rule and one or more CHECK constraints exist for a column or columns, all restrictions are evaluated.
Additional Constraint Information
An index created for a constraint cannot be dropped with the DROP INDEX statement; the constraint must be dropped with the ALTER TABLE statement. An index created for and used by a constraint can be rebuilt with the DBCC DBREINDEX statement.


Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint. The constraint name appears in any error message about constraint violations.


When a constraint is violated in an INSERT, UPDATE, or DELETE statement, the statement is terminated. However, the transaction (if the statement is part of an explicit transaction) continues to be processed. You can use the ROLLBACK TRANSACTION statement with the transaction definition by checking the @@ERROR system function.
If a table has FOREIGN KEY or CHECK CONSTRAINTS and triggers, the constraint conditions are evaluated before the trigger is executed.

For a report on a table and its columns, use sp_help or sp_helpconstraint. To rename a table, use sp_rename. For a report on the views and stored procedures that depend on a table, use sp_depends.

Space is generally allocated to tables and indexes in increments of one extent at a time. When the table or index is created, it is allocated pages from mixed extents until it has enough pages to fill a uniform extent. After it has enough pages to fill a uniform extent, another extent is allocated each time the currently allocated extents become full. For a report about the amount of space allocated and used by a table, execute sp_spaceused.

Nullability Rules Within a Table Definition
The nullability of a column determines whether or not that column can allow a null value (NULL) as the data in that column. NULL is not zero or blank: it means no entry was made or an explicit NULL was supplied, and it usually implies that the value is either unknown or not applicable.

When you create or alter a table with the CREATE TABLE or ALTER TABLE statements, database and session settings influence and possibly override the nullability of the data type used in a column definition. It is recommended that you always explicitly define a column as NULL or NOT NULL for noncomputed columns or, if you use a user-defined data type, that you allow the column to use the default nullability of the data type.

When not explicitly specified, column nullability follows these rules:

If the column is defined with a user-defined data type:
SQL Server uses the nullability specified when the data type was created. Use sp_help to get the default nullability of the data type.
If the column is defined with a system-supplied data type:
If the system-supplied data type has only one option, it takes precedence. timestamp data types must be NOT NULL.


If the setting of sp_dbcmptlevel is 65 or lower, bit data types default to NOT NULL if the column does not have an explicit NULL or NOT NULL. For more information, see sp_dbcmptlevel.


If any session settings are ON (turned on with the SET statement), then:
If ANSI_NULL_DFLT_ON is ON, NULL is assigned.

If ANSI_NULL_DFLT_OFF is ON, NOT NULL is assigned.

If any database settings are configured (changed with sp_dboption), then:
If ANSI null default is true, NULL is assigned.

If ANSI null default is false, NOT NULL is assigned.

When neither of the ANSI_NULL_DFLT options is set for the session and the database is set to the default (ANSI null default is false), then the SQL Server default of NOT NULL is assigned.


If the column is a computed column, its nullability is always determined automatically by SQL Server. Use the COLUMNPROPERTY function (AllowsNull property) to find out the nullability of such a column.


Note The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server both default to having ANSI_NULL_DFLT_ON set to ON. ODBC and OLE DB users can configure this in ODBC data sources, or with connection attributes or properties set by the application.


Permissions
CREATE TABLE permission defaults to the members of the db_owner and db_ddladmin fixed database roles. Members of the db_owner fixed database role and members of the sysadmin fixed server role can transfer CREATE TABLE permission to other users.

Examples
A. Use PRIMARY KEY constraints
This example shows the column definition for a PRIMARY KEY constraint with a clustered index on the job_id column of the jobs table (allowing the system to supply the constraint name) in the pubs sample database.

job_id smallint
PRIMARY KEY CLUSTERED

This example shows how a name can be supplied for the PRIMARY KEY constraint. This constraint is used on the emp_id column of the employee table. This column is based on a user-defined data type.

emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED

B. Use FOREIGN KEY constraints
A FOREIGN KEY constraint is used to reference another table. Foreign keys can be single-column keys or multicolumn keys. This example shows a single-column FOREIGN KEY constraint on the employee table that references the jobs table. Only the REFERENCES clause is required for a single-column FOREIGN KEY constraint.

job_id smallint NOT NULL
DEFAULT 1
REFERENCES jobs(job_id)

You can also explicitly use the FOREIGN KEY clause and restate the column attribute. Note that the column name does not have to be the same in both tables.

FOREIGN KEY (job_id) REFERENCES jobs(job_id)

Multicolumn key constraints are created as table constraints. In the pubs database, the sales table includes a multicolumn PRIMARY KEY. This example shows how to reference this key from another table; an explicit constraint name is optional.

CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)
REFERENCES sales (stor_id, ord_num, title_id)

C. Use UNIQUE constraints
UNIQUE constraints are used to enforce uniqueness on nonprimary key columns. A PRIMARY KEY constraint column includes a restriction for uniqueness automatically; however, a UNIQUE constraint can allow null values. This example shows a column called pseudonym on the authors table. It enforces a restriction that authors' pen names must be unique.

pseudonym varchar(30) NULL
UNIQUE NONCLUSTERED

This example shows a UNIQUE constraint created on the stor_name and city columns of the stores table, where the stor_id is actually the PRIMARY KEY; no two stores in the same city should be the same.

CONSTRAINT U_store UNIQUE NONCLUSTERED (stor_name, city)

D. Use DEFAULT definitions
Defaults supply a value (with the INSERT and UPDATE statements) when no value is supplied. In the pubs database, many DEFAULT definitions are used to ensure that valid data or placeholders are entered.

On the jobs table, a character string default supplies a description (column job_desc) when the actual description is not entered explicitly.

DEFAULT 'New Position - title not formalized yet'

In the employee table, the employees can be employed by an imprint company or by the parent company. When an explicit company is not supplied, the parent company is entered (note that, as shown here, comments can be nested within the table definition).

DEFAULT ('9952')
/* By default the Parent Company Publisher is the company
to whom each employee reports. */

In addition to constants, DEFAULT definitions can include functions. Use this example to get the current date for an entry:

DEFAULT (getdate())

Niladic-functions can also improve data integrity. To keep track of the user who inserted a row, use the niladic-function for USER (do not surround the niladic-functions with parentheses):

DEFAULT USER

E. Use CHECK constraints
This example shows restrictions made to the values entered into the min_lvl and max_lvl columns of the jobs table. Both of these constraints are unnamed:

CHECK (min_lvl >= 10)

and

CHECK (max_lvl <= 250)

This example shows a named constraint with a pattern restriction on the character data entered into the emp_id column of the employee table.

CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')

This example specifies that the pub_id must be within a specific list or follow a given pattern. This constraint is for the pub_id of the publishers table.

CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]')

F. Complete table definitions
This example shows complete table definitions with all constraint definitions for three tables (jobs, employee, and publishers) created in the pubs database.

/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
job_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL
DEFAULT 'New Position - title not formalized yet',
min_lvl tinyint NOT NULL
CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL
CHECK (max_lvl <= 250)
)

/* ************************* employee table ************************* */
CREATE TABLE employee
(
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 through 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL
DEFAULT 1
/* Entry job_id for new hires. */
REFERENCES jobs(job_id),
job_lvl tinyint
DEFAULT 10,
/* Entry job_lvl for new hires. */
pub_id char(4) NOT NULL
DEFAULT ('9952')
REFERENCES publishers(pub_id),
/* By default, the Parent Company Publisher is the company
to whom each employee reports. */
hire_date datetime NOT NULL
DEFAULT (getdate())
/* By default, the current system date is entered. */
)

/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
pub_id char(4) NOT NULL
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]'),
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL
DEFAULT('USA')
)

G. Use the uniqueidentifier data type in a column
This example creates a table with a uniqueidentifier column. It uses a PRIMARY KEY constraint to protect the table against users inserting duplicated values, and it uses the NEWID() function in the DEFAULT constraint to provide values for new rows.

CREATE TABLE Globally_Unique_Data
(guid uniqueidentifier
CONSTRAINT Guid_Default
DEFAULT NEWID(),
Employee_Name varchar(60),
CONSTRAINT Guid_PK PRIMARY KEY (Guid)
)

H. Use an expression for a computed column
This example illustrates the use of an expression ((low + high)/2) for calculating the myavg computed column.

CREATE TABLE mytable
(
low int,
high int,
myavg AS (low + high)/2
)

I. Use the USER_NAME function for a computed column
This example uses the USER_NAME function in the myuser_name column.

CREATE TABLE mylogintable
(
date_in datetime,
user_id int,
myuser_name AS USER_NAME()
)

J. Use NOT FOR REPLICATION
This example shows using the IDENTITY property on a table subscribed to a replication. The table includes a CHECK constraint to ensure that the SaleID values generated on this system do not grow into the range assigned to the replication Publisher.

CREATE TABLE Sales
(SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,
CHECK NOT FOR REPLICATION (SaleID <= 199999),
SalesRegion CHAR(2),
CONSTRAINT ID_PK PRIMARY KEY (SaleID)
)


See Also

ALTER TABLE

COLUMNPROPERTY

CREATE INDEX

CREATE RULE

CREATE VIEW

Data Types

DROP INDEX

DROP RULE

DROP TABLE

sp_addtype

sp_depends

sp_help

sp_helpconstraint

sp_rename

sp_spaceused

©1988-2000 Microsoft Corporation. All Rights Reserved.

---

quote:

---

Transact-SQL Reference


ALTER TABLE
Modifies a table definition by altering, adding, or dropping columns and constraints, or by disabling or enabling constraints and triggers.

Syntax
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}

< column_definition > ::=
{ column_name data_type }
[ [ DEFAULT constant_expression ] [ WITH VALUES ]
| [ IDENTITY [ (seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL ]
[ COLLATE < collation_name > ]
[ < column_constraint > ] [ ...n ]

< column_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}

< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON {filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression
[ FOR column ] [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}

Arguments
table

Is the name of the table to be altered. If the table is not in the current database or owned by the current user, the database and owner can be explicitly specified.

ALTER COLUMN

Specifies that the given column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel.

The altered column cannot be:

A column with a text, image, ntext, or timestamp data type.


The ROWGUIDCOL for the table.


A computed column or used in a computed column.


A replicated column.


Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.


Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.


Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.


Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.


Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.
Some data type changes may result in a change in the data. For example, changing an nchar or nvarchar column to char or varchar can result in the conversion of extended characters. For more information, see CAST and CONVERT. Reducing the precision and scale of a column may result in data truncation.

column_name

Is the name of the column to be altered, added, or dropped. For new columns, column_name can be omitted for columns created with a timestamp data type. The name timestamp is used if no column_name is specified for a timestamp data type column.

new_data_type

Is the new data type for the altered column. Criteria for the new_data_type of an altered column are:

The previous data type must be implicitly convertible to the new data type.


new_data_type cannot be timestamp.


ANSI null defaults are always on for ALTER COLUMN; if not specified, the column is nullable.


ANSI padding is always on for ALTER COLUMN.


If the altered column is an identity column, new_data_type must be a data type that supports the identity property.


The current setting for SET ARITHABORT is ignored. ALTER TABLE operates as if the ARITHABORT option is ON.
precision

Is the precision for the specified data type. For more information about valid precision values, see Precision, Scale, and Length.

scale

Is the scale for the specified data type. For more information about valid scale values, see Precision, Scale, and Length.

COLLATE < collation_name >

Specifies the new collation for the altered column. Collation name can be either a Windows collation name or a SQL collation name. For a list and more information, see Windows Collation Name and SQL Collation Name.

The COLLATE clause can be used to alter the collations only of columns of the char, varchar, text, nchar, nvarchar, and ntext data types. If not specified, the column is assigned the default collation of the database.

ALTER COLUMN cannot have a collation change if any of the following conditions apply:

If a check constraint, foreign key constraint, or computed columns reference the column changed.


If any index, statistics, or full-text index are created on the column. Statistics created automatically on the column changed will be dropped if the column collation is altered.


If a SCHEMABOUND view or function references the column.
For more information about the COLLATE clause, see COLLATE.

NULL | NOT NULL

Specifies whether the column can accept null values. Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified. A new column added to a table must either allow null values, or the column must be specified with a default value.

If the new column allows null values and no default is specified, the new column contains a null value for each row in the table. If the new column allows null values and a default definition is added with the new column, the WITH VALUES option can be used to store the default value in the new column for each existing row in the table.

If the new column does not allow null values, a DEFAULT definition must be added with the new column, and the new column automatically loads with the default value in the new columns in each existing row.

NULL can be specified in ALTER COLUMN to make a NOT NULL column allow null values, except for columns in PRIMARY KEY constraints. NOT NULL can be specified in ALTER COLUMN only if the column contains no null values. The null values must be updated to some value before the ALTER COLUMN NOT NULL is allowed, such as:

UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL

If NULL or NOT NULL is specified with ALTER COLUMN, new_data_type [(precision [, scale ])] must also be specified. If the data type, precision, and scale are not changed, specify the current column values.

[ {ADD | DROP} ROWGUIDCOL ]

Specifies the ROWGUIDCOL property is added to or dropped from the specified column. ROWGUIDCOL is a keyword indicating that the 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.

The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. It also does not automatically generate values for new rows inserted into the table. To generate unique values for each column, either use the NEWID function on INSERT statements or specify the NEWID function as the default for the column.

ADD

Specifies that one or more column definitions, computed column definitions, or table constraints are added.

computed_column_expression

Is an expression that defines the value of a computed column. A computed column is a virtual column not physically stored in the table but computed from an expression using other columns in the same table. For example, a computed column could have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations where regular expressions can be used, with these exceptions:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a+b may be indexed but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.


Note Because each row in a table may have different values for columns involved in a computed column, the computed column may not have the same result for each row.

n

Is a placeholder indicating that the preceding item can be repeated n number of times.

WITH CHECK | WITH NOCHECK

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY and UNIQUE constraints.

If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. This is not recommended except in rare cases. The new constraint will be evaluated in all future updates. Any constraint violations suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

Constraints defined WITH NOCHECK are not considered by the query optimizer. These constraints are ignored until all such constraints are re-enabled using ALTER TABLE table CHECK CONSTRAINT ALL.

DROP { [CONSTRAINT] constraint_name | COLUMN column_name }

Specifies that constraint_name or column_name is removed from the table. DROP COLUMN is not allowed if the compatibility level is 65 or earlier. Multiple columns and constraints can be listed. A column cannot be dropped if it is:

A replicated column.


Used in an index.


Used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.


Associated with a default defined with the DEFAULT keyword, or bound to a default object.


Bound to a rule.
{ CHECK | NOCHECK} CONSTRAINT

Specifies that constraint_name is enabled or disabled. When disabled, future inserts or updates to the column are not validated against the constraint conditions. This option can only be used with FOREIGN KEY and CHECK constraints.

ALL
Specifies that all constraints are disabled with the NOCHECK option, or enabled with the CHECK option.
{ENABLE | DISABLE} TRIGGER

Specifies that trigger_name is enabled or disabled. When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled.

ALL
Specifies that all triggers in the table are enabled or disabled.

trigger_name
Specifies the name of the trigger to disable or enable.
column_name data_type

Is the data type for the new column. data_type can be any Microsoft® SQL Server™ or user-defined data type.

DEFAULT

Is a keyword that specifies the default value for the column. DEFAULT definitions can be used to provide values for a new column in the existing rows of data. DEFAULT definitions cannot be added to columns that have a timestamp data type, an IDENTITY property, an existing DEFAULT definition, or a bound default. If the column has an existing default, the default must be dropped before the new default can be added. To maintain compatibility with earlier versions of SQL Server, it is possible to assign a constraint name to a DEFAULT.

IDENTITY

Specifies that the new column is an identity column. When a new row is added to the table, SQL Server 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 to a tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) column. Only one identity column can be created per table. The DEFAULT keyword and bound defaults cannot be used with an identity column. Either both the seed and increment must be specified, or neither. If neither are specified, the default is (1,1).

Seed
Is the value used for the first row loaded into the table.

Increment
Is the incremental value added to the identity value of the previous row loaded.
NOT FOR REPLICATION

Specifies that the IDENTITY property should not be enforced when a replication login, such as sqlrepl, inserts data into the table. NOT FOR REPLICATION can also be specified on constraints. The constraint is not checked when a replication login inserts data into the table.

CONSTRAINT

Specifies the beginning of a PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK constraint, or a DEFAULT definition.

constraint_name

Is the new constraint. Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint.

PRIMARY KEY

Is a constraint that enforces entity integrity for a given column or columns through a unique index. Only one PRIMARY KEY constraint can be created for each table.

UNIQUE

Is a constraint that provides entity integrity for a given column or columns through a unique index.

CLUSTERED | NONCLUSTERED

Specifies that a clustered or nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED; UNIQUE constraints default to NONCLUSTERED.

If a clustered constraint or index already exists on a table, CLUSTERED cannot be specified in ALTER TABLE. If a clustered constraint or index already exists on a table, PRIMARY KEY constraints default to NONCLUSTERED.

WITH FILLFACTOR = fillfactor

Specifies how full SQL Server should make each index page used to store the index data. User-specified fillfactor values can be from 1 through 100. If a value is not specified, the default is 0. A lower fillfactor value creates an index with more space available for new index entries without having to allocate new space. For more information, see CREATE INDEX.

ON {filegroup | DEFAULT}

Specifies the storage location of the index created for the constraint. If filegroup is specified, the index is created in the named filegroup. If DEFAULT is specified, the index is created in the default filegroup. If ON is not specified, the index is created in the filegroup that contains the table. If ON is specified when adding a clustered index for a PRIMARY KEY or UNIQUE constraint, the entire table is moved to the specified filegroup when the clustered index is created.

DEFAULT, in this context, is not a keyword. DEFAULT is an identifier for the default filegroup and must be delimited, as in ON "DEFAULT" or ON [DEFAULT].

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 table referenced by the FOREIGN KEY constraint.

ref_column

Is a column or list of columns in parentheses referenced by the new FOREIGN KEY constraint.

ON DELETE {CASCADE | NO ACTION}

Specifies what action occurs to a row in the table altered, if 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 if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back.

The CASCADE action ON DELETE cannot be defined if an INSTEAD OF trigger ON DELETE already exists on the table in question.

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 checks for one or more dependent rows in the Orders table. If any exist, the dependent row in the Orders table will be deleted, as well as the row referenced in the Customers table.

On the other hand, if NO ACTION is specified, SQL Server raises an error and rolls back the delete action on the Customers row if there is at least one row in the Orders table that references it.

ON UPDATE {CASCADE | NO ACTION}

Specifies what action occurs to a row in the table altered, if 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 that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.

The CASCADE action ON UPDATE cannot be defined if an INSTEAD OF trigger ON UPDATE already exists on the table in question.

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 checks for one or more dependent rows in the Orders table. If any exist, the dependent row in the Orders table will be updated, as well as the row referenced in the Customers table.

On the other hand, if NO ACTION is specified, SQL Server raises an error and rolls back the update action on the Customers row if there is at least one row in the Orders table that references it.

[ASC | DESC]

Specifies the order in which the column or columns participating in table constraints are sorted. The default is ASC.

WITH VALUES

Specifies that the value given in DEFAULT constant_expression is stored in a new column added to existing rows. WITH VALUES can be specified only when DEFAULT is specified in an ADD column clause. If the added column allows null values and WITH VALUES is specified, the default value is stored in the new column added to existing rows. If WITH VALUES is not specified for columns that allow nulls, the value NULL is stored in the new column in existing rows. If the new column does not allow nulls, the default value is stored in new rows regardless of whether WITH VALUES is specified.

column[,...n]

Is a column or list of columns in parentheses used in a new constraint.

constant_expression

Is a literal value, a NULL, or a system function used as the default column value.

FOR column

Specifies the column associated with a table-level DEFAULT definition.

CHECK

Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

logical_expression

Is a logical expression used in a CHECK constraint and returns TRUE or FALSE. Logical_expression used with CHECK constraints cannot reference another table but can reference other columns in the same table for the same row.

Remarks
To add new rows of data, use the INSERT statement. To remove rows of data, use the DELETE or TRUNCATE TABLE statements. To change the values in existing rows, use UPDATE.

The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to ensure no other connections reference even the meta data for the table during the change. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects a large number of rows.

If there are any execution plans in the procedure cache referencing the table, ALTER TABLE marks them to be recompiled on their next execution.

If the ALTER TABLE statement specifies changes on column values referenced by other tables, either of two events occurs depending on the action specified by ON UPDATE or ON DELETE in the referencing tables.

If no value or NO ACTION (the default) is specified in the referencing tables, an ALTER TABLE statement against the parent table that causes a change to the column value referenced by the other tables will be rolled back and SQL Server raises an error.


If CASCADE is specified in the referencing tables, changes caused by an ALTER TABLE statement against the parent table are applied to the parent table and its dependents.
ALTER TABLE statements that add a sql_variant column can generate the following warning:

The total row size (xx) for table 'yy' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
This warning occurs because sql_variant can have a maximum length of 8016 bytes. When a sql_variant column contains values close to the maximum length, it can overshoot the row's maximum size limit.

The restrictions that apply to ALTER TABLE statements on tables with schema bound views are the same as the restrictions currently applied when altering tables with a simple index. Adding a column is allowed. However, removing or changing a column that participates in any schema bound view is not allowed. If the ALTER TABLE statement requires altering a column used in a schema bound view, the alter action fails and SQL Server raises an error message. For more information about SCHEMABINDING and indexed views, see CREATE VIEW.

Adding or removing triggers on base tables is not affected by creating a schema bound view referencing the tables.

Indexes created as part of a constraint are dropped when the constraint is dropped. Indexes that were created with CREATE INDEX must be dropped with the DROP INDEX statement. The DBCC DBREINDEX statement can be used to rebuild an index part of a constraint definition; the constraint does not need to be dropped and added again with ALTER TABLE.

All indexes and constraints based on a column must be removed before the column can be removed.

When constraints are added, all existing data is verified for constraint violations. If any violations occur, the ALTER TABLE statement fails and an error is returned.

When a new PRIMARY KEY or UNIQUE constraint is added to an existing column, the data in the column(s) must be unique. If duplicate values are found, the ALTER TABLE statement fails. The WITH NOCHECK option has no effect when adding PRIMARY KEY or UNIQUE constraints.

Each PRIMARY KEY and UNIQUE constraint generates an index. The number of UNIQUE and PRIMARY KEY constraints cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.

If a column is added having a uniqueidentifier data type, it can be defined with a default that uses the NEWID() function to supply the unique identifier values in the new column for each existing row in the table.

SQL Server does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.

The ALTER COLUMN clause of ALTER TABLE does not bind or unbind any rules on a column. Rules must be bound or unbound separately using sp_bindrule or sp_unbindrule.

Rules can be bound to a user-defined data type. CREATE TABLE then automatically binds the rule to any column defined having the user-defined data type. ALTER COLUMN does not unbind the rule when changing the column data type. The rule from the original user-defined data type remains bound to the column. After ALTER COLUMN has changed the data type of the column, any subsequent sp_unbindrule execution that unbinds the rule from the user-defined data type does not unbind it from the column for which data type was changed. If ALTER COLUMN changes the data type of a column to a user-defined data type bound to a rule, the rule bound to the new data type is not bound to the column.

Permissions
ALTER TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

Examples
A. Alter a table to add a new column
This example adds a column that allows null values and has no values provided through a DEFAULT definition. Each row will have a NULL in the new column.

CREATE TABLE doc_exa ( column_a INT)
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO

B. Alter a table to drop a column
This example modifies a table to remove a column.

CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
GO
ALTER TABLE doc_exb DROP COLUMN column_b
GO
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO

C. Alter a table to add a column with a constraint
This example adds a new column with a UNIQUE constraint.

CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
GO
EXEC sp_help doc_exc
GO
DROP TABLE doc_exc
GO

D. Alter a table to add an unverified constraint
This example adds a constraint to an existing column in the table. The column has a value that violates the constraint; therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow the constraint to be added.

CREATE TABLE doc_exd ( column_a INT)
GO
INSERT INTO doc_exd VALUES (-1)
GO
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1)
GO
EXEC sp_help doc_exd
GO
DROP TABLE doc_exd
GO

E. Alter a table to add several columns with constraints
This example adds several columns with constraints defined with the new column. The first new column has an IDENTITY property; each row in the table has new incremental values in the identity column.

CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)
GO
ALTER TABLE doc_exe ADD

/* Add a PRIMARY KEY identity column. */
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,

/* Add a column referencing another column in the same table. */
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),

/* Add a column with a constraint to enforce that */
/* nonnull data is in a valid phone number format. */
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d IS NULL OR
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE
"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),

/* Add a nonnull column with a default. */
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081
GO
EXEC sp_help doc_exe
GO
DROP TABLE doc_exe
GO

F. Add a nullable column with default values
This example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.

ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES

G. Disable and reenable a constraint
This example disables a constraint that limits the salaries accepted in the data. WITH NOCHECK CONSTRAINT is used with ALTER TABLE to disable the constraint and allow an insert that would normally violate the constraint. WITH CHECK CONSTRAINT re-enables the constraint.

CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)

-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)

-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)

H. Disable and reenable a trigger
This example uses the DISABLE TRIGGER option of ALTER TABLE to disable the trigger and allow an insert that would normally violate the trigger. It then uses ENABLE TRIGGER to re-enable the trigger.

CREATE TABLE trig_example
(id INT,
name VARCHAR(10),
salary MONEY)
go
-- Create the trigger.
CREATE TRIGGER trig1 ON trig_example FOR INSERT
as
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
print "TRIG1 Error: you attempted to insert a salary > $100,000"
ROLLBACK TRANSACTION
END
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (1,"Pat Smith",100001)
GO
-- Disable the trigger.
ALTER TABLE trig_example DISABLE TRIGGER trig1
GO
-- Attempt an insert that would normally violate the trigger
INSERT INTO trig_example VALUES (2,"Chuck Jones",100001)
GO
-- Re-enable the trigger.
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (3,"Mary Booth",100001)
GO


See Also

DROP TABLE

sp_help

©1988-2000 Microsoft Corporation. All Rights Reserved.

---