I am working on limiting input in certain fields in my Postgres database, and I have a working solution using ENUM
like so:
CREATE TYPE label AS ENUM ('foo','bar');
However, I was browsing around and looking at alternative ways of doing this using:
- constraints (eg.
CHECK (type IN ('foo','bar'))
) - triggers (slow, from what I've read)
- foreign keys
What are the advantages/disadvantages of using either of these methods, would any of these be more advisable? I am not necessarily asking which one is the 'best' method, only what kind of issues may arise from using these, ie. I know that using triggers might be quite slow compared to the other methods, but perhaps could deal with some more complex input conditions.
Best Answer
Maybe not all the differences, but most important in my opinion :
enum vs text/varchar + check constraint.
enum ('small', 'big')
small < big; for text column'big'<'small'
enum vs foreign key
Triggers are just not the right tool to implement check constraints. They add complexity, hide logic, add maintenance overhead. In some cases they are "necessary evil" , but they definitely shouldn't be used just to do what check constraint can do.