Creating UNIQUE and PRIMARY KEY Constraints on Computed Columns

  • Post author:
  • Post category:
  • Post comments:0 Comments
  • Post last modified:January 3, 2005

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

Author Details
Kubernetes, DevSecOps, AWS, 클라우드 보안, 클라우드 비용관리, SaaS 의 활용과 내재화 등 소프트웨어 개발 전반에 도움이 필요하다면 도움을 요청하세요. 지인이라면 가볍게 도와드리겠습니다. 전문적인 도움이 필요하다면 저의 현업에 방해가 되지 않는 선에서 협의가능합니다.
0 0 votes
Article Rating
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments