When you attempt to enter data into a table that has a fully enabled CHECK constraint, you will only be successful if the data doesn’t violate that constraint. If you attempt to enter invalid data, the operation will fail with an error.

But what if you find yourself in the situation where you really must insert data that will violate the CHECK constraint? Perhaps the constraint no longer applies, or maybe you have an exception where one row is allowed to bypass the constraint. Either way, you won’t be able to enter anything outside the rules of the constraint.

If you find yourself in this situation, you can always disable the constraint. Here’s how to do that using Transact-SQL.

Continue reading →

If you have a CHECK constraint in SQL Server that is currently disabled, you can use the code below to re-enable it.

When you enable a CHECK constraint (or a foreign key constraint for that matter), you have the option to specify whether or not to check any existing data in the table.

Below are code examples of enabling a CHECK constraint, while specifying each of these different options.

Continue reading →

In SQL Server you can create a CHECK constraint in a table to specify the data values that are acceptable in one or more columns.

If a table has a CHECK constraint on it, and you try to provide data that doesn’t conform to the CHECK constraint, the operation will fail with an error.

This helps to maintain data integrity, because it helps to prevent invalid data from entering the database.

When you create a CHECK constraint, you provide a logical expression that returns TRUE or FALSE. This logical expression is what’s used to check the data.

CHECK constraints are similar to foreign key constraints because they control the values that are put in a column. However, 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.

Constraints can be defined at the column level or table level. A column-level constraint applies to just the data in that column. A table-level constraint applies to the whole row, and checks data from multiple columns.

Below are examples of creating both column-level and table-level CHECK constraints.

Continue reading →

In SQL Server, a constraint defines rules that data in a database must comply with. For example, you could have a UNIQUE constraint applied to a column to ensure that any value inserted into that column is unique (i.e. no other row shares the same value).

If later on you need to remove that constraint, here’s how to do it using Transact-SQL.

Continue reading →

This article demonstrates how to drop a column in a SQL Server database, when that column has an existing constraint (Hint: You need to drop the constraint first).

Continue reading →

When using SQL Server, sometimes you need to modify an existing table. For the purposes of this article, say you want to add a DEFAULT constraint to an existing column.

To add a DEFAULT constraint to an existing column, use the ALTER TABLE statement and specify the column and the specific constraint that you want to apply.

Continue reading →