Data Management

Constrain your SQL Server data with T-SQL

Constraining SQL Server column values with T-SQL


The process of creating data constraints via the graphical interfaces provided with Microsoft SQL Server was covered in the article "TechRepublic Tutorial: Enforce data integrity with constraints and Enterprise Manager." While this approach is straightforward, it's not the only method available. The SQL Server version of SQL, T-SQL, provides ample commands to create all the constraints necessary in a database application. This article examines the syntax of using these commands.

Constraints
Constraints come in various flavors. The following list contains a quick overview of each type of constraint available in SQL Server:
  • PRIMARY KEY—A column or combination of columns whose values uniquely identify each row in the table
  • FOREIGN KEY—A column or combination of columns used to establish and enforce a link between the data in two tables
  • UNIQUE—Ensures that no duplicate values are entered in specific columns that do not participate in a primary key
  • CHECK—Enforces domain integrity by limiting the values that are accepted by a column
  • DEFAULT—Defines column values stored when no value has been assigned
  • Nullability—Designates that a column will accept null values

Each constraint type has an associated T-SQL statement format with various implementations. Let’s take a closer look at each constraint type with examples.

Primary keys
You can add primary keys to a database table during table creation or afterwards. The process of adding a primary key during table creation uses the CREATE TABLE T-SQL command. The following example illustrates its use:
CREATE TABLE BuilderTable (
ColumnID INT IDENTITY(100000,1),
ColumnValue VARCHAR(50),
CONSTRAINT ID_PK PRIMARY KEY (ColumnID)
)


This simple T-SQL statement creates a table called BuilderTable with two columns. The first column has a unique value (IDENTITY) and has been set up as a primary key with the constraint line. The constraint keyword is followed by a name to assign to it, the constraint type (PRIMARY KEY), and the column name to which it is associated in parentheses.

If the table already exists, you can use the ADD command to add a new primary key constraint. It follows the same format as adding a new column to the table:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY


The following example shows how it is used:
ALTER TABLE BuilderTable
ADD CONSTRAINT ID_PK PRIMARY KEY (ColumnID)


When a new PRIMARY KEY or UNIQUE constraint is added to an existing column, the column data must be unique. The statement fails if duplicate values are found. Each PRIMARY KEY and UNIQUE constraint generates an index. Tables are limited to one primary key, so existing primary keys should be removed before issuing the ADD command. Deleting a constraint is simple using the DROP CONSTRAINT command:
ALTER TABLE BuilderTable
DROP CONSTRAIN ID_PK


The prevalence of primary keys often leads to the creation of foreign keys to ensure data integrity.

Foreign keys
Foreign keys resemble primary keys in their ability to be added before or after table creation. Actually, the syntax is consistent as well, as you can see in Listing A.

T-SQL creates our table with an identity column that is both a primary key for the existing table and a foreign key that references another table and column. The CONSTRAINT, FOREIGN KEY, and REFERENCES keywords are used to create the foreign key. The REFERENCES keyword precedes the table and column names for the foreign key container. FOREIGN KEY constraints are added and dropped using the same syntax:
ALTER TABLE BuilderTable
DROP CONSTRAINT FK_ID


And adding a FOREIGN KEY constraint looks like this:
ALTER TABLE BuilderTable
ADD CONSTRAINT FK_ID FOREIGN KEY REFERENCES BuilderTable2(ColumnID)


Foreign keys facilitate data consistency and the incorporation of data from one table into another. Both foreign and primary keys are based on the concept of unique values. That is, a column cannot be a key value if its values are not unique. Otherwise, how would a data value be located?

Unique values
In addition to the key constraints, you may need additional columns to contain only unique values. You can achieve this with the UNIQUE constraint. Again, you can add it to a table during creation or modification, as shown in Listing B.

Once the table has been created, you can use the ALTER TABLE command to add additional constraints:
ALTER TABLE BuilderTable
ADD CONSTRAINT ID_UNIQ_1 UNIQUE (ColumnValue)


Likewise, you can remove existing constraints from a table:
ALTER TABLE BuilderTable
DROP CONSTRAINT ID_UNIQ_1


Each UNIQUE constraint generates an index. The number of UNIQUE constraints cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and one clustered index. If clustered or nonclustered is not specified for a UNIQUE constraint, nonclustered is used by default.

Check those values
CHECK constraints limit the values accepted by a column and control the values that are placed in a column. An acceptable value or range of values is specified. CHECK constraints determine the valid values from a logical expression that is not based on data in another column. The following example demonstrates the use of a CHECK constraint that ensures a column value is less than a specific value:
CREATE TABLE BuilderTable( ColumnID INT IDENTITY(100000,1),
ColumnValueVARCHAR(50),
ColumnValue2 int NOT NULL CHECK (ColumnValue2 <= 1000), CONSTRAINT ID_PK PRIMARY KEY (ColumnID)
)

Default values
Often, there are default values that should be assigned to database fields. The DEFAULT constraint makes this an easy process. You can assign default values during table creation or when modifying a column. Listing C shows you the syntax.

The column TestColumn is added as an integer with a default value of 10. That is, if a new row is added, a value of 10 is assigned if no value is specified. Additionally, you can use the ALTER TABLE command to add new columns to an existing table with default values or edit an existing column in the table:
ALTER TABLE BuilderTable
ADD TestColumn2 int DEFAULT (400)


Nullability
The last constraint type, Nullability, involves whether or not table columns may store null values. Null values can often cause problems with data, but optional fields are common so null values are allowed in these particular fields. You can specify whether a column can store null values when a table is created via the NOT NULL and NULL keywords. They follow the column name data type, as shown in Listing D.

The column definition for ColumnValue2 does not allow null values. The column may be altered to allow null values by way of the ALTER TABLE command:
ALTER TABLE BuilderTable
ALTER COLUMN ColumnValue2 int NULL


NOT NULL can be specified in ALTER COLUMN only if the column contains no null values. The null values must be updated to some value before ALTER COLUMN NOT NULL is allowed.

Also, you can add new columns to the table. If the new column does not allow null values, you must add a DEFAULT definition with the new column, and the new column will automatically load with the default value in the new columns in each existing row.

Naming
Constraint naming is another point to consider. Constraint names are specific to a database and are visible throughout the database container. For this reason, each constraint name must be unique. If not, an error message will be returned during constraint creation.

The data you need
Constraints make it possible to restrict or control the values stored in individual fields throughout a database. This is important to ensuring the integrity of the data. You can create and/or manipulate constraints by using the graphical tools included with SQL Server or by using the simple text of T-SQL.

Editor's Picks