locked
Primary Key on Calculated Column RRS feed

  • Question

  • I am runnign into an issue with using computed columns. I have a table composed of five columns, and I would like to concatenate two of those columns to create a primary key column.

    I create the computed column fine, but when I try to make that column the primary key, SQL Server balks! I keep getting an error message saying I can't create a primary key on a nullable column. SQL Server keeps changing the nullability of the computed column.

    When I try to make the computed column not accet NULL's SQL Server balks again saying the attribute can't be updated...???

    Is it just not possible to create a primary key on  computed column??


    A. M. Robinson

    Tuesday, June 19, 2012 6:09 PM

All replies

  • Here are the parameters under which you can make a computed column a PK.  Question is, what are the data types and nullability of the columns that you are concatanating?

    http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx

    A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns. 

    For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

      • A computed column cannot be the target of an INSERT or UPDATE statement.

        The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.

      >

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Edited by Robin_Ren Monday, May 27, 2013 4:35 PM
    Tuesday, June 19, 2012 6:21 PM
  • Ugg - that post is all messed up and it won't let me delete it.


    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, June 19, 2012 6:22 PM
  • E.g.

    USE tempdb;
    
    CREATE TABLE Test 
    ( 
    	ID AS a + b PERSISTED NOT NULL PRIMARY KEY, 
    	a INT NOT NULL, 
    	b INT NOT NULL
    );
    See also Indexes on Computed Columns. I think you missed one of the requirements.

    Tuesday, June 19, 2012 6:25 PM
  • Nice, you killed the layout ;)
    Tuesday, June 19, 2012 6:25 PM
  • Nice, you killed the layout ;)

    Yup, had this happen once before and it seems to happen when copying and pasting from certain MS pages. 

    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, June 19, 2012 6:27 PM
  • Chuck,

    Try if you can use Robert's instructions from this thread http://social.msdn.microsoft.com/Forums/en-US/reportabug/thread/17952ae6-c056-4eec-a220-0bda22d3a5f7


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, June 19, 2012 6:36 PM
  • One column is an int and the other is a smallint - both columns are non-nullable. AP_ID is an int and AP_SEQ is a smallint.

    This is the formula I'm using for the computed column:

    ((CONVERT([varchar](25),[AP_ID],(0))+'-')+CONVERT([varchar](25),[AP_SEQ],(0)))

    
    

    A. M. Robinson


    • Edited by ansonee Tuesday, June 19, 2012 7:21 PM more info
    Tuesday, June 19, 2012 7:17 PM
  • I'm runjing in SQL 2012 but have no issues creating a table like this:

    CREATE TABLE Testzzz
    ( 
    	ID AS ((CONVERT([varchar](25),[AP_ID],(0))+'-')+CONVERT([varchar](25),[AP_SEQ],(0))) PERSISTED NOT NULL PRIMARY KEY, 
    	[AP_ID] INT NOT NULL, 
    	[AP_SEQ] SMALLINT NOT NULL
    );


    Chuck Pedretti | Magenic – North Region | magenic.com

    Tuesday, June 19, 2012 7:34 PM
  • This works:

    USE tempdb;
    
    CREATE TABLE Test 
    ( 
    	ID AS CAST(a AS VARCHAR(25)) + '-' + +CAST(b AS VARCHAR(25)) PERSISTED NOT NULL PRIMARY KEY, 
    	a INT NOT NULL, 
    	b SMALLINT NOT NULL
    );

    Tuesday, June 19, 2012 8:00 PM
  • this would do the job
    USE tempdb;

    CREATE TABLE Test21 

    ID AS CAST(a AS VARCHAR(25)) + '-' + +CAST(b AS VARCHAR(25)) PERSISTED NOT NULL PRIMARY KEY, 
    a INT NOT NULL, 
    b SMALLINT NOT NULL
    );

    SQL Champ
    Database Consultants NY

    Wednesday, June 20, 2012 6:50 AM
  • It's for the a data warehouse...the key needs to have some kind of "meaning" to it...

    It foreign keys to several tables throughout the database


    A. M. Robinson

    Wednesday, June 20, 2012 3:19 PM
  • The primary key data will never change. It is comprised of a transaction_id and a sequence_id. A transaction can have any number of sequences in it...

    A. M. Robinson

    Wednesday, May 1, 2013 1:06 AM
  • Any progress?

    Apply INT IDENTITY SURROGATE PRIMARY KEY and place UNIQUE index (or KEY) on transaction_id and  sequence_id computed column.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
    • Edited by Kalman Toth Wednesday, May 8, 2013 10:46 PM
    Wednesday, May 8, 2013 10:42 PM