Post reply

Need composite unique key

  • October 29, 2009 at 5:28 am

    #139711

    Assume a table with the following columns

    ColumnDatatype

    ID-- int

    Year-- smallint

    effectivedate-- datetime

    Now, I want to create Composite unique key on Year and Month(effectivedate). ie) year and month of effectivedate should not repeat in this table. Please suggest some solutions.

  • October 29, 2009 at 5:43 am

    #1072357

    Define a computed column as the year and the month, persist that column and stick a unique index on it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 29, 2009 at 5:47 am

    #1072359

    But i need date value too. Do I need to create additional column for month and create unique key. is there no other way?

  • October 29, 2009 at 5:56 am

    #1072362

    Sorry, I don't understand. I'm not saying drop the date column.

    Define a computed (calculated) column, based on the effective date column then create a unique index on the computed column.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 29, 2009 at 6:28 am

    #1072380

    Thanks gail shaw.. Its working

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply