Post reply

Table design

  • June 12, 2012 at 1:11 pm

    #259240

    I have a table:

    id int

    project_id int

    primary bit

    with several entries per project --

    is there any way to set up a constraint that (project_id, primary) can only happen once when primary = true?

    BT

  • June 12, 2012 at 1:21 pm

    #1500010

    Express12 (6/12/2012)

    I have a table:

    id int

    project_id int

    primary bit

    with several entries per project --

    is there any way to set up a constraint that (project_id, primary) can only happen once when primary = true?

    I would eliminate that column and create a table that only contained the primary id

    CREATE TABLE primaryProject (project_id int primary key, id int)

    Jared
    CE - Microsoft

  • June 12, 2012 at 1:26 pm

    #1500015

    Sure.

    CREATE UNIQUE NONCLUSTERED INDEX idx_Projects_ProjectIDPrimary (project_id, primary)

    WHERE Primary = 1

    p.s. Primary is a reserved word and probably should not be used for a column name. Call it something like IsPrimary.

    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

  • June 12, 2012 at 1:43 pm

    #1500022

    You should look at setting up a table constraint.

Viewing 4 posts - 1 through 3 (of 3 total)

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