ALTER TABLE

Transact-SQL Reference

previous page next page
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

previous page start next page