Search code examples
mysqltriggersunique

MySQL constraint/trigger to prevent duplicated rows?


Is there a performance difference between using unique constraint and trigger to prevent duplicated rows in MySQL?


Solution

  • Monitor it. But well, the outcome should be obvious.

    As the unique index exists specifically to enforce this constraint, it should be the first choice.

    By using the trigger you would have to do additional operations to even check if there is the dataset (tablescan vs index lookup, or you set an index without constraint on the column but...), then react to it accordingly. So if there is nothing else you are trying to do (logging the failed attempt maybe), this would be unnecessary steps.