Tag: constraints

Find Out if a CHECK Constraint is Column-Level or Table-Level in SQL Server (T-SQL Examples)

Posted on by Ian

When you create a CHECK constraint in SQL Server, you might not even think about whether it’s a table-level constraint or a column-level constraint.

A table-level CHECK constraint applies to the table, whereas a column-level constraint applies to a specific column. With a table-level CHECK constraint, it’s the row that is checked when it checks the data. With a column-level CHECK constraint, it’s the specific column that is checked.

Generally you’ll know whether or not the constraint you’re creating is a table-level or column-level constraint by the definition you give it. If only one column is being checked in the expression, it will be a column-level constraint. Otherwise it will be a table-level constraint.

But how do you know if your existing constraints are column-level or table-level?

You can run any of the code examples below to determine whether your existing constraints are column-level or table-level. These retrieve all CHECK constraints for the current database, but you can always use a WHERE clause to narrow it down to a specific constraint.

Continue reading →

How to Create a CHECK Constraint in SQL Server (T-SQL Examples)

Posted on by Ian

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 →

How to Add a Foreign Key Constraint to an Existing Table in SQL Server (T-SQL)

Posted on by Ian

In database terms, a foreign key is a column that is linked to another table‘s primary key field in a relationship between two tables.

A foreign key is a type of constraint, and so if you want to create a foreign key in SQL Server, you’ll need to create a foreign key constraint.

This article demonstrates how to create a foreign key constraint in SQL Server, using Transact-SQL.

Continue reading →

How to Drop a Constraint in SQL Server (T-SQL)

Posted on by Ian

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 →

How to Add a DEFAULT Constraint to an Existing Column in SQL Server

Posted on by Ian

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 →