#0287 – SQL Server – Database Design – Can a Computed Column be used as a Primary Key? [Msg 1711]


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

image

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.

Advertisements

2 thoughts on “#0287 – SQL Server – Database Design – Can a Computed Column be used as a Primary Key? [Msg 1711]

  1. marc_jellinek@hotmail.com

    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.

    Like

    Reply
  2. Nakul Vachhrajani

    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.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s