Constraint on multiple columns

  • 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

  • Can you create a unique index on the table?

    is your Primary key correct ?


  • 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?

  • 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


  • 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