Creating UNIQUE and PRIMARY KEY Constraints on Computed Columns
Posted: July 02, 2001
This tip is one of a series from SQL Server Magazine. For additional tips, visit the SQL Server Tips and Tricks Center.
Q. Can you create UNIQUE and PRIMARY KEY constraints on computed columns in SQL Server 2000?
A. In SQL Server, the physical mechanism that UNIQUE and PRIMARY KEY constraints use to enforce uniqueness is a unique index. Because SQL Server 2000 supports indexes on computed columns, you can create UNIQUE and PRIMARY KEY constraints on computed columns.
Defining a UNIQUE constraint on a computed column is a straightforward process, as the following example shows:
CREATE TABLE T1 (
col1 int NOT NULL,
col2 AS col1 + 1 UNIQUE
)
However, if you define a PRIMARY KEY on a computed column, such as:
CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS col1 + 1 PRIMARY KEY
)
you receive the following error:
Server: Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘T2’.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Because of the primary key constraint, SQL Server requires you to guarantee that your computation’s result will not be NULL. The computation in the computed column can overflow (for example, when you add 1 to the largest integer) or underflow (when you subtract 1 from the smallest integer), and other computations can result in a divide-by-zero error. However, if the ARITHABORT (which determines whether a query has ended when an overflow or a divide-by-zero error occurs) and ANSI_WARNINGS (which specifies ANSI SQL-92 standard behavior for several error conditions) session settings are off, instead of ending the query, the computation can have a NULL result.
In practice, when either ARITHABORT or ANSI_WARNINGS settings is off, you cannot create an index on a computed column or insert values into a table that has an index on a computed column because SQL Server detects such an attempt and returns an error. But SQL Server still requires you to guarantee that the computation will not result in NULL values. The trick is to wrap the computed column’s computation with the ISNULL() function and supply an alternative value if the computation results in NULL:
CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS ISNULL(col1 + 1, 0) PRIMARY KEY
)
REFERENCE: SQL Server Tips and Tricks Center
