Tag: t-sql

What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server

Posted on by Ian

If you ever find yourself in the situation where you need to re-enable a CHECK constraint that has previously been disabled, you should definitely make sure that you know what you’re doing.

In particular, you should understand the difference between WITH NOCHECK and WITH CHECK arguments.

These arguments can be used at the time you enable the constraint. They specify whether or not existing data is validated against your re-enabled (or newly added) CHECK constraint. Basically you have the option of checking all existing data for any violations against the constraint. If you don’t specify anything, existing data won’t be checked. That’s why it’s important to understand how it works.

By the way, these arguments also apply to foreign key constraints.

Continue reading →

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 Create a Foreign Key in SQL Server (T-SQL Examples)

Posted on by Ian

In this article I demonstrate how to create a foreign key in SQL Server using Transact-SQL. I demonstrate how to create a foreign key at the time of creating the table (as opposed to updating an existing table).

A foreign key is a column that references another table’s primary key column. This creates a relationship between the tables.

Continue reading →

11 Ways to Return Foreign Keys in a SQL Server Database using T-SQL

Posted on by Ian

This article presents eleven different approaches to retrieving foreign key information in SQL Server. Some of these will be more useful than others, depending on the situation.

Some of these methods can be modified to return further information. For example, you could perform joins on various system views to return more data.

Continue reading →

11 Ways to Retrieve a Primary Key in SQL Server (T-SQL Examples)

Posted on by Ian

There are often many ways to do the same (or similar) thing in SQL Server. Retrieving all primary keys from a database (or even just the primary key for a given table) is a case in point.

This article presents eleven different approaches to retrieving primary key information in SQL Server.

Some of these will be more useful than others, depending on your exact situation.

Continue reading →

How to Create a Primary Key in SQL Server (T-SQL Examples)

Posted on by Ian

This article demonstrates how to create a primary key in SQL Server when creating a table using Transact-SQL.

A primary key is one or more columns that have been configured as the unique identifier for a given table. Primary keys can be used to enforce data integrity in the table.

A table can only have one primary key, and primary keys can only be added to columns that are defined as NOT NULL.

This article demonstrates how to create a primary key in a new table (i.e. when creating the table). If you need to create a primary key in an existing table, see How to Add a Primary Key to an Existing Table in SQL Server.

Continue reading →

How to Add a Primary Key to an Existing Table in SQL Server (T-SQL Examples)

Posted on by Ian

This article demonstrates how to add a primary key to an existing table in SQL Server using Transact-SQL.

A primary key is a column that has been configured as the unique identifier for a given table.

You would normally create a primary key constraint when you create the table, but you can also add a primary key to an existing table.

Note that a table can only have one primary key. So you can’t add a primary key if the table already has one.

Also primary keys can only be added to columns that are defined as NOT NULL.

Continue reading →