Unique Constraints and Check Constraints

  • Article
  • 5 minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database

UNIQUE constraints and CHECK constraints are two types of constraints that can be used to enforce data integrity in SQL Server tables. These are important database objects.

This topic contains the following sections.

UNIQUE Constraints

Cheque Constraints

Related Tasks

UNIQUE Constraints

Constraints are rules that the SQL Server Database Engine enforces for yous. For example, you can use UNIQUE constraints to brand sure that no duplicate values are entered in specific columns that exercise non participate in a main key. Although both a UNIQUE constraint and a Master KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a Chief Primal constraint when you lot want to enforce the uniqueness of a cavalcade, or combination of columns, that is not the primary key.

Unlike PRIMARY Key constraints, UNIQUE constraints allow for the value NULL. Even so, as with whatever value participating in a UNIQUE constraint, only one nothing value is allowed per column. A UNIQUE constraint tin can exist referenced by a FOREIGN KEY constraint.

When a UNIQUE constraint is added to an existing cavalcade or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure all values are unique. If a UNIQUE constraint is added to a column that has duplicated values, the Database Engine returns an error and does non add the constraint.

The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an effort to insert a duplicate row is made, the Database Engine returns an fault bulletin that states the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered alphabetize is explicitly specified, a unique, nonclustered index is created past default to enforce the UNIQUE constraint.

CHECK Constraints

CHECK constraints enforce domain integrity by limiting the values that are accustomed by one or more than columns. Y'all can create a CHECK constraint with any logical (Boolean) expression that returns Truthful or FALSE based on the logical operators. For example, the range of values for a salary column can be limited past creating a CHECK constraint that allows for just data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range. The logical expression would be the post-obit: salary >= 15000 AND salary <= 100000.

You tin can utilise multiple Check constraints to a unmarried column. You tin also apply a single Bank check constraint to multiple columns past creating it at the table level. For example, a multiple-column CHECK constraint could be used to ostend that any row with a country_region cavalcade value of Usa likewise has a two-character value in the state cavalcade. This allows for multiple conditions to be checked in ane location.

Bank check constraints are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The deviation is in how they decide which values are valid: Foreign KEY constraints obtain the list of valid values from another table, while CHECK constraints determine the valid values from a logical expression.

Caution

Constraints that include implicit or explicit data type conversion may cause certain operations to neglect. For example, such constraints defined on tables that are sources of partition switching may cause an Alter TABLE...SWITCH operation to fail. Avoid information blazon conversion in constraint definitions.

Limitations of CHECK Constraints

CHECK constraints turn down values that evaluate to False. Considering zip values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you lot identify a constraint on an int column MyColumn specifying that MyColumn tin can incorporate only the value ten (MyColumn=x). If y'all insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an mistake.

A Bank check constraint returns True when the status it is checking is non Faux for whatsoever row in the table. A CHECK constraint works at the row level. If a table that has just been created does non have any rows, any CHECK constraint on this table is considered valid. This situation tin can produce unexpected results, as in the following example.

              CREATE Tabular array CheckTbl (col1 int, col2 int);   GO   CREATE Function CheckFnctn()   RETURNS int   Equally    BEGIN      DECLARE @retval int      SELECT @retval = COUNT(*) FROM CheckTbl      Render @retval   Finish;   GO   Modify TABLE CheckTbl   ADD CONSTRAINT chkRowCount Bank check (dbo.CheckFnctn() >= 1 );   Get

The Bank check constraint being added specifies that there must be at least one row in table CheckTbl. Nonetheless, because in that location are no rows in the table against which to check the condition of this constraint, the Alter TABLE statement succeeds.

CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with sure types of check constraints may produce unexpected results. For example, consider the post-obit statements executed on table CheckTbl.

              INSERT INTO CheckTbl VALUES (x, x);   Become   DELETE CheckTbl WHERE col1 = 10;

The DELETE argument succeeds, even though the CHECK constraint specifies that table CheckTbl must have at least 1 row.

Related Tasks

Notation

If the table is published for replication, y'all must make schema changes using the Transact-SQL statement Modify TABLE or SQL Server Management Objects (SMO). When schema changes are fabricated using the Table Designer or the Database Diagram Designer, it attempts to driblet and recreate the table. Yous cannot drop published objects, therefore the schema change will fail.

Chore Topic
Describes how to create a unique constraint. Create Unique Constraints
Describes how to modify a unique constraint. Modify Unique Constraints
Describes how to delete a unique constraint. Delete Unique Constraints
Describes how to disable a cheque constraint when a replication agent inserts or updates information in your table. Disable Check Constraints for Replication
Describes how to disable a cheque constraint when data is added to, updated in, or deleted from a table. Disable Check Constraints with INSERT and UPDATE Statements
Describes how to change the constraint expression or the options that enable or disable the constraint for specific weather. Change Check Constraints
Describes how to delete a check constraint. Delete Cheque Constraints
Describes how to view the properties of a check constraint. Unique Constraints and Check Constraints