The CHECK constraint being added specifies that there must be at least one row in table CheckTbl. CREATE TABLE CheckTbl (col1 int, col2 int) ĪDD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 ) This situation can produce unexpected results, as in the following example. If a table that has just been created does not have any rows, any CHECK constraint on this table is considered valid. A CHECK constraint works at the row level. If you insert the value NULL into M圜olumn, the Database Engine inserts NULL and does not return an error.Ī CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table. For example, suppose you place a constraint on an int column M圜olumn specifying that M圜olumn can contain only the value 10 ( M圜olumn=10). Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. Limitations of CHECK ConstraintsĬHECK constraints reject values that evaluate to FALSE. Avoid data type conversion in constraint definitions. For example, such constraints defined on tables that are sources of partition switching may cause an ALTER TABLE.SWITCH operation to fail. The difference is in how they determine 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.Ĭonstraints that include implicit or explicit data type conversion may cause certain operations to fail. This allows for multiple conditions to be checked in one location.ĬHECK constraints are similar to FOREIGN KEY constraints in that they control the values that are put in a column. For example, a multiple-column CHECK constraint could be used to confirm that any row with a country_region column value of USA also has a two-character value in the state column. You can also apply a single CHECK constraint to multiple columns by creating it at the table level. You can apply multiple CHECK constraints to a single column. The logical expression would be the following: salary >= 15000 AND salary <= 100000. This prevents salaries from being entered beyond the regular salary range. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. CHECK ConstraintsĬHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint. Therefore, if an attempt to insert a duplicate row is made, the Database Engine returns an error message that states the UNIQUE constraint has been violated and does not add the row to the table. The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. If a UNIQUE constraint is added to a column that has duplicated values, the Database Engine returns an error and does not add the constraint. When a UNIQUE constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure all values are unique. A UNIQUE constraint can be referenced by a FOREIGN KEY constraint. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. Unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key. For example, you can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. This topic contains the following sections.Ĭonstraints are rules that the SQL Server Database Engine enforces for you. UNIQUE constraints and CHECK constraints are two types of constraints that can be used to enforce data integrity in SQL Server tables. Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |