Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: UNIQUE Constraint on two specific column values

    Folks

    I have a table that has

    CustomerID (PK, FK, INT, NOT NULL)
    AddressReference (PK, FK, UNIQEIDENTIFIER, NOT NULL)
    PrimaryAddress (Bit, NOT NULL
    PriorityID (Int, NULL)

    I would like to Create a UNIQUE Constraint ON

    CustomerID, PrimaryAddress = 1, PriorityID = 1

    ie. A Customer can only have ONE Address record that has Both Primary = 1 and PriorityID = 1

    Is there an easy way to enforce this, maybe with a calculated field?

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Depends on your version of SQL Server. On recent versions (I think since 2008) you can do something like this:

    Code:
    CREATE UNIQUE INDEX idx_check ON your_table (customerId, AddressReference)
    WHERE primaryAddress = 1 AND priorityId = 1

  3. #3
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    You can also put check constraint on the two columns with value "1" and then create a unique constraint on customerId field
    Last edited by jassi.singh; 10-18-11 at 03:04.

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Thanks for your help Guys, That's just the ticket

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    GWilly,

    Create a view off of your table:

    Code:
    create view CustomerPriorityConstraint as
    select CustomerID, AddressReference from [YourTable] where PrimaryAddress  = 1 and PriorityID = 1
    Then, change this to an Indexed View and place a set CustomerID/AddressReference as the unique primary key.

    Indexed views can often be used to enforce difficult record-based constraints.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2011
    Posts
    71
    Hello ,I have an idea like Blindman mentioned ,But one difference ,you can concatenate to columns you want them to be unique then alias tem ,But the result as i guess should be 11 not 1
    Try,I wish I'm hopefully to you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •