Post reply

Constraint on multiple columns

  • February 26, 2020 at 4:35 pm

    #3728976

    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

  • February 26, 2020 at 4:50 pm

    #3728981

    Can you create a unique index on the table?

    is your Primary key correct ?

    MVDBA

  • February 26, 2020 at 5:59 pm

    #3729014

    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?

  • February 27, 2020 at 9:25 am

    #3729193

    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

  • February 27, 2020 at 11:31 am

    #3729207

    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.

  • February 27, 2020 at 12:22 pm

    This was removed by the editor as SPAM

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

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