Search code examples
postgresqldatabase-designtriggerspostgresql-9.1check-constraints

Trigger vs. check constraint

I want to add a field-level validation on a table. There is a field named "account_number" and this field should always pass a "luhn" check. I've found a function called "luhn_verify" that seems to work properly (google for it if you are interested). It returns a boolean. My question is:

Are there any major performance advantages in PostgreSQL for using a trigger for this validation vs. a check constraint.

Additional information:

  • PostgreSQL 9.1
  • Table does not currently have an insert trigger, but does have an update.

Disclaimers:

I feel like this has probably already been answered, but I can't seem to find a distinct answer. If so, please mark as duplicate and reference original question/answer.

Might be a better questions for the dba board.

Solution

  • The rule of thumb is to use CHECK constraint when possible.

    A CHECK constraint is faster, simpler, more portable, needs less code and is less error prone. Triggers can easily be circumvented by other triggers, for instance.

    A TRIGGER is more complicated. Use it when you have to, for more complex requirements.

    If a CHECK constraint is too restrictive for your case or causes trouble reloading a dump, you could use the NOT VALID modifier as middle ground (Postgres 9.2+). And, optionally, VALIDATE it later. See: