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.
Msg 1750, Level 16, State 0, Line 2
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
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,
In most cases, this is a horrible idea.
Using a computed column as a primary or candidate key violates the rules of normalization.
By definition, a computed column is dependent on the data used in the calculation.
By definition, a key should not be dependent on data. The data should be dependent on a key.
By definition, a primary key should not change. Should the data used in the calculation change, so would the key. This will either result in a cascading change to FKs dependent on the PK or result in orphaned records.
That being said, I have seen surrogate keys in dimension tables calculated as a checksum or hash of dimension attributes. The understanding is the dimension members attributes will never change and thus the checksum or hash used as the key will also never change. I understand the practicality of this, but generally do not agree with it.
Now that I think of it, calculated surrogate keys in dimension tables do make sense in a way. Thank-you for sharing that scenario, Marc.
For an OLTP system though, I agree with you that it’s not a good idea.