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.
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.
A composite primary key is a primary key consisting of multiple columns. Microsoft usually refers to these as multicolumn primary keys in its documentation.
This article provides an example of creating a composite primary key using Transact-SQL in SQL Server.
A composite foreign key is a foreign key consisting of multiple columns.
This article provides an example of creating a composite foreign key using Transact-SQL in SQL Server.
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.
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.
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.
In addition to the many data types available in SQL Server, you also have the option of creating your own data type. Some of these are referred to as “user-defined data types”, while others are referred to as “alias data types”.
A user-defined data type is implemented through a class of an assembly in the Microsoft.NET Framework common language runtime (CLR).
An alias data type is based on a SQL Server native system type. In other words, you use an existing data type for the basis of your alias data type.
Having said that, I’ve seen Microsoft use the term “user-defined data type alias” when referring to an alias data type. I’ve also seen it referred to as simply a “data type alias”.
Either way, this article demonstrates how to a create a user-defined data type alias using Transact-SQL.
If you ever need to get a list of data types in SQL Server, you can use one of the system views to do just that.
In particular, you can use the sys.types system catalog view. This view returns all system-supplied and user-defined data types defined in the database. If you’re using SQL Server 2000 sys.systypes should do the trick.
Here are a couple of ways to return information on all languages in SQL Server. One method uses a system compatibility view, the other method executes a system stored procedure. Both methods return the same result.