ALTER TABLE Statement (version 6.5)

Disables a foreign key constraint and defers constraints during replication. Constraints can be deferred without being dropped.

For additional syntax information for the ALTER TABLE statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

ALTER TABLE [database.[owner.]]table_name
[WITH {CHECK | NOCHECK}]
    {{CHECK | NOCHECK} CONSTRAINT {constraint_name | ALL}
    |
    [ADD
        {col_name column_properties [column_constraints]
        | [[, ] table_constraint]}
            [, {next_col_name | next_table_constraint}]...]
    |    
    [DROP CONSTRAINT]
    constraint_name [, constraint_name2]...]}

where

table_name

Specifies which table to alter. You can specify local or global temporary tables, but FOREIGN KEY constraints are not enforced on temporary tables.

WITH {CHECK | NOCHECK}

Allows CHECK and FOREIGN KEY constraints to be added to a table without verifying existing data for constraint violations.

{NOCHECK | CHECK} CONSTRAINT

Turns the constraint checking on or off for foreign key and check constraints.

constraint_name | ALL

Specifies an individual constraint or all constraints to modify.

You can defer constraints for the table, including CHECK and FOREIGN KEY constraints, by using the ALL keyword. However, you cannot defer primary and unique key constraints; these constraints must be dropped.

Important Deferring constraints on a table does not defer constraints on other tables that refer to the altered tables. Updates to the table can still raise constraint violation errors.

ADD

Specifies that a column- or table-level constraint be added to an existing table.

col_name

Is a new column for the table. Column names must conform to the rules for identifiers and must be unique in the table.

column_properties =

datatype [NULL | IDENTITY[(seed, increment)]]

datatype

Specifies the datatype of the column. System or user-defined datatypes are acceptable. Columns added to a table must be defined as NULL.

IDENTITY(seed, increment)

Specifies values for existing rows based on the seed and increment parameters.

table_constraints=

Specifies up to one PRIMARY KEY constraint per table, one DEFAULT constraint per column, and any number of FOREIGN KEY, UNIQUE, or CHECK constraints on any column or columns. All can be entered within the same ALTER TABLE statement.

For a table-level constraint:

    [CONSTRAINT constraint_name]
    { PRIMARY KEY [CLUSTERED | NONCLUSTERED]
        (col_name [, col_name2[..., col_name16]])
    | UNIQUE [CLUSTERED | NONCLUSTERED]
        (col_name [, col_name2[..., col_name16]])
        [WITH FILLFACTOR = fillfactor]
    | FOREIGN KEY (col_name[, col_name2[..., col_name16]])
        REFERENCES [owner.] ref_table (ref_col[, ref_col2[..., ref_col16]])
        [NOT FOR REPLICATION]
    | CHECK [NOT FOR REPLICATION] (expression)}

CONSTRAINT constraint_name

Specifies the constraint within the database. Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If this clause is omitted, a system-generated name is assigned to the constraint.

PRIMARY KEY | UNIQUE [CLUSTERED | NONCLUSTERED]

Specifies entity integrity for a given column or columns, which can be used as primary keys.

WITH FILLFACTOR

Specifies how full SQL Server makes each index page when creating an index with existing data.

SORTED_DATA | SORTED_DATA_REORG

Specify that the sort that is performed when a clustered index is created be eliminated. Both options verify that the data has been sorted by checking each index value to determine whether it is higher than the previous one. If any row fails this check, the ALTER TABLE statement terminates. You can either fix the data or rerun the ALTER TABLE statement without the SORTED_DATA option (the data will be sorted but not reorganized).

When the data satisfies the checks, the SORTED_DATA option will always be faster than the SORTED_DATA_REORG because the data is not copied and nonclustered indexes will not be rebuilt.

SORTED_DATA_REORG differs from SORTED_DATA because it physically reorganizes the data. This option is useful when a FILLFACTOR is specified to compact or expand the pages on which a table is stored. The effects of these options change slightly if used with the ON segment_name option.

Reorganizing the data is a good idea when a table becomes fragmented. To determine whether or not a table is contiguous, use the DBCC statement's SHOW_CONTIG. For details about what causes table fragmentation and recommended solutions, see the DBCC statement.

ON segment_name

Specifies the segment on which to create the index.

column_constraints=

Is up to one unique or foreign key constraint, one DEFAULT constraint, and any number of check constraints for each new or existing column.

For a column-level constraint:

    [CONSTRAINT constraint_name]
    { PRIMARY KEY [CLUSTERED | NONCLUSTERED] [(col_name)]
    | UNIQUE [CLUSTERED | NONCLUSTERED] [(col_name)]
        [WITH FILLFACTOR = fillfactor]
    | FOREIGN KEY [(col_name)]
        REFERENCES [(ref_col)]
        [NOT FOR REPLICATION]
    | CHECK [NOT FOR REPLICATION] (expression)}

FOREIGN KEY [NOT FOR REPLICATION]

Specifies single column or multicolumn referential integrity. When defined, the number of columns and datatypes of each column specified in the FOREIGN KEY clause must identically match the columns in the REFERENCES clause. Values entered in this column or columns must exist in the table, and column(s) defined in the REFERENCES clause and the referenced table's columns must have a PRIMARY KEY or UNIQUE constraint defined on them. For column-level constraints, using the FOREIGN KEY and col_name identifiers are optional.

NOT FOR REPLICATION

Specifies that column-level constraints be suspended during replication.

Remarks

The ALTER TABLE NOCHECK statement allows replication to take place without permanently removing referential integrity constraints. Replication needs deferred constraints because constraints are sometimes violated by modifying a database with INSERT and DELETE statements. Use the ALTER TABLE statement to temporarily suspend constraints checking.

Two conditions cause an error to be returned: when ALL is specified and no constraints exist and when the specified constraint does not exist.

Once all data modifications are made, you can use the ALTER TABLE statement to reestablish constraint checking.

For more information about foreign keys or the ALTER TABLE statement, see the Microsoft SQL Server Transact-SQL Reference.

Examples

A.    Add a Constraint

This example reviews adding a constraint. The foreign key constraint pub_id is added to the titles table.

ALTER TABLE titles
ADD
CONSTRAINT FK_pub_id FOREIGN KEY (pub_id) 
REFERENCES publishers(pub_id)
  

B.    Disable a Constraint

This example disables the ssn_constraint in the authors table.

ALTER TABLE authors
    NOCHECK CONSTRAINT ssn_constraint
  

C.    Enable a Constraint

This example enables the ssn_constraint in the authors table.

ALTER TABLE authors
    CHECK CONSTRAINT ssn_constraint
  

Permission

Only the system administrator, the database owner, and the table owner can use the ALTER TABLE statement.