Recently, I wrote a post on using Regular expressions in CHECK constraints. Based on this post, I was asked a very interesting question:

Can a computed column have a primary key constraint defined on it?

Quite frankly, I had never encountered such a situation. I did not have an answer to the question off the top of my head and I therefore requested some time for research. This week-end, I ran a small test in order to get an answer to this question. This post is the answer to the question I was asked.

Attempting to define a primary key on a non-persisted computed column

The script provided below has a computed column (non-persisted), which I am attempting to define as a primary key:

USE tempdb;
GO
IF OBJECT_ID('tempdb..#ComputedColumnAsPk') IS NOT NULL
    DROP TABLE #ComputedColumnAsPk;
GO

CREATE TABLE #ComputedColumnAsPk (RecordId INT IDENTITY(1,1),
                                  RecordValue VARCHAR(20),
                                  ComputedColumn AS ('AWC' + CAST(RecordId AS VARCHAR(10))),
                                  CONSTRAINT pk_ComputedColumnsAsPK PRIMARY KEY CLUSTERED (ComputedColumn)
                                 );
GO

Execution of the script yields the following error message:

Msg 1711, Level 16, State 1, Line 2

Cannot define PRIMARY KEY constraint on column ‘ComputedColumn’ in table ‘#ComputedColumnAsPk’. The computed column has to be persisted and not nullable.

Could not create constraint. See previous errors.

Changes required to define the primary key

Reading the error message in detail tells us that the primary key constraint creation failed because:

• The column was not persisted
• The column is not nullable

Now, we know that the computed column consists of a static string and the RecordId column. The RecordId is an IDENTITY column, and based on the rules for IDENTITY columns it is automatically a NOT NULL column.

All that remains therefore is to convert the non-persisted computed column to a persisted computed column.

Executing the script provided below succeeds and we can validate that by checking the primary key definition (also provided in the script below):

IF OBJECT_ID('tempdb..#ComputedColumnAsPk') IS NOT NULL
    DROP TABLE #ComputedColumnAsPk;
GO

CREATE TABLE #ComputedColumnAsPk (RecordId INT IDENTITY(1,1),
                                  RecordValue VARCHAR(20),
                                  ComputedColumn AS ('AWC' + CAST(RecordId AS VARCHAR(10))) PERSISTED,
                                  CONSTRAINT pk_ComputedColumnsAsPK PRIMARY KEY CLUSTERED (ComputedColumn)
                                 );
GO

--Using sp_help to get the table design and verify that the primary key was indeed created
sp_help #ComputedColumnAsPk
GO

--Get the Computed Column definition
SELECT scc.is_persisted,* 
FROM sys.computed_columns AS scc
WHERE scc.name = 'ComputedColumn';
GO

Conclusion

As we can see through this little experiment, it is possible to have a primary key constraint defined on a computed column provided the column is not nullable and is persisted.

I am yet to come across a scenario in the projects I handle where I would need computed column as a primary key. If you, the kind reader has come across such a situation, please let us know by your comments in the comments section below.

Until we meet next time,

Be courteous. Drive responsibly.