SQLServer – Only Unique or primary Key Constraints can be created on Computed Columns

Error Message

Experiencing the error message listed below:


Msg 8183, Level 16, State 1, Line ###

Only UNIQUE or PRIMARY KEY constraints can be created on computed columns

 

Code

SQL


use [tempdb]
go

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
SET ARITHABORT ON
GO

if OBJECT_ID('dbo.InfoLog') is not null
begin
	DROP TABLE [dbo].[InfoLog]
end
go

/*
  errored on the create table statement
*/
CREATE TABLE [dbo].[InfoLog]
(
	  [id] bigint not null
	, [idGroup]  AS  len([id]) null	
)

GO

Output


Msg 8183, Level 16, State 1, Line 7
Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints 
require that computed columns be persisted.

SQL

If I do not specify the Nullability argument on the idGroup column, we are OK

Code


/*
  no errors on the create table statement
  as we did not specify null
*/
CREATE TABLE [dbo].[InfoLog]
(
	  [id] bigint not null
	, [idGroup]  AS  (len([id]) persisted
)

GO

 

Code

If we specify the Not Null on the idGroup column, we are OK


/*
  no errors on the create table statement
  as we did not specify null
*/
CREATE TABLE [dbo].[InfoLog]
(
	  [id] bigint not null
	, [idGroup]  AS  (len([id])) 
              persisted not null
)

GO

Comments:

  • The error statements specifies that not null constraints are not allowed; we are specifying a null constraint at worst
  • If we choose not to specify column nullability our definition will not be as precise

Help!

 

References

  • Marking persisted computed columns NOT NULL in SQL Server Management Studio
    Link


Additional Reading

  • Computed Columns and Divide by Zero
    Link

Leave a Reply

Please log in using one of these methods to post your comment:

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