Constraint on multiple columns

  • SIJCOOKE

    SSC Veteran

    Points: 270

    Hi,

    I want to restrict users from adding duplicate rows. Is there a way to put one constraint on multiple columns, or use a trigger to do this, maybe BEFORE insert/ BEFORE update?

    Thanks S

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    Can you create a unique index on the table?

    is your Primary key correct ?

    MVDBA

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717790

    A trigger will work, but it is more overhead and not better than an index, as Mike noted. Is there some action you want to occur here other than raising an error?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    it's a little bit like prevention vs abortion.. triggers will abort the process after the fact. a unique index or pk will kill it before it starts

    you can use some for of reporting in XE that tells you when the duplicate row errors

    but also if you are using a proc to add the duplicate row, maybe a try catch block with a raiserror statement... much better than a trigger

    MVDBA

  • Jonathan AC Roberts

    SSCoach

    Points: 17204

    A unique constraint is the standard way to prevent duplicates, though I usually just create a unique index instead.

    alter table myTable
    add constraint UC_LocoBrakeType_Col1Col2 unique (Col1, Col2)

    Adding a unique constraint also adds an index so there is no performance benefit over creating a unique index to creating a unique constraint.

  • This was removed by the editor as SPAM

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

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