Jaa

Creating and Modifying CHECK Constraints

  • Artikkeli

You can create a CHECK constraint as part of the table definition when you create a table. If a table already exists, you can add a CHECK constraint. Tables and columns can contain multiple CHECK constraints.

If a CHECK constraint already exists, you can modify or delete it. For example, you may want to modify the expression that is used by the CHECK constraint on a column in the table.

Note

To modify a CHECK constraint, you must first delete the existing CHECK constraint and then re-create it with the new definition.

The following Transact-SQL example creates a new table, and then modifies the table by adding a CHECK constraint to the CreditRating column.

IF OBJECT_ID ('dbo.Vendors', 'U') IS NOT NULL
DROP TABLE dbo.Vendors;
GO
CREATE TABLE dbo.Vendors 
    (VendorID int PRIMARY KEY, VendorName nvarchar (50), 
    CreditRating tinyint)
GO
ALTER TABLE dbo.Vendors ADD CONSTRAINT CK_Vendor_CreditRating
    CHECK (CreditRating >= 1 AND CreditRating <= 5)

Delete a CHECK constraint to remove the limitations on acceptable data values in the column or columns included in the constraint expression.

To create a CHECK constraint when you create a table

To create a CHECK constraint on an existing table

To delete a CHECK constraint

Forcing a CHECK Constraint by Using WITH NOCHECK

When a CHECK constraint is added to an existing table, the CHECK constraint can apply to new data only or to existing data. By default, the CHECK constraint applies to both existing data and any new data. Use the WITH NOCHECK option of the ALTER TABLE statement to apply the new constraint only to newly added data. This option is useful when the existing data already meets the new CHECK constraint, or when a business rule requires the constraint to be enforced only from this point forward.

For example, an old constraint may require that postal codes be limited to five digits but a new constraint requires nine-digit postal codes. Old data with five-digit postal codes is still valid and will coexist with new data that contains nine-digit postal codes. Therefore, only new data should be checked against the new constraint.

However, you should be careful when you add a constraint without checking existing data because this bypasses the controls in the SQL Server 2005 Database Engine that enforce the integrity rules for the table.

To prevent checking of existing data when you create a CHECK constraint

Disabling CHECK Constraints

You can disable existing CHECK constraints for specific operations, such as INSERT operations, UPDATE operations, and replication processing.

  • INSERT and UPDATE statements
    Disabling a CHECK constraint enables data in the table to be modified without being validated by the constraints. Disable a CHECK constraint during INSERT and UPDATE statements if new data will violate the constraint or if the constraint should apply only to the data already in the database.
  • Replication processing
    Disable a CHECK constraint during replication if the constraint is specific to the source database. When a table is replicated, the table definition and data are copied from the source database to a destination database. These two databases are typically, but not necessarily, on separate servers. If the CHECK constraints specific to the source database are not disabled, they may unnecessarily prevent new data from being entered in the destination database. For more information, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

To disable a CHECK constraint for INSERT and UPDATE statements

To disable a CHECK constraint for replication

To obtain information about CHECK constraints

See Also

Concepts

CHECK Constraints

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the Transact-SQL example.