Re: FK, CHECK, ENUM or DOMAIN

Joshua Drake published his fascinating researches about pros and cons of using ENUMs, DOMAINs, FOREIGN KEY and CHECK constraints. Since I haven’t found a way to leave a comment I’m writing this post.

Joshua wrotes:

A DOMAIN for this problem suffers from the same problems as ENUM as it registers as a type. However a DOMAIN is more flexible as you can apply complex logic to the validation (just as you can with a CHECK). For example a DOMAIN could contain the regex to validate if a email address is correctly formed. I have used domains many times in the past to create complex validating types. They are useful.

In addition I could say that DOMAIN is more flexible because it can have several CHECK constraints. Hence, DOMAIN doesn’t suffer much from being a type. In case of ENUM we must drop the whole type to change its values, but with DOMAIN we may just drop needed CONSTRAINT(s) and(or) create new one.

Let’s imagine situation when we need to add or remove some values to pass validating:

--create domain
CREATE DOMAIN my_domain AS text 
  CONSTRAINT my_domain_check CHECK(VALUE IN ('foo1','foo2'));

--add additional values
ALTER DOMAIN my_domain 
  ADD CONSTRAINT my_domain_check2 CHECK(VALUE IN ('foo3','foo4'));

--remove some values
ALTER DOMAIN my_domain 
  DROP CONSTRAINT my_domain_check;

--list available constraints information
SELECT conname, pg_get_constraintdef(pg_constraint.oid) 
  FROM pg_constraint, pg_type 
  WHERE 
     pg_constraint.contypid = pg_type.oid 
    AND 
     pg_type.oid = 'my_domain'::regtype;

--P.S. Do not like JOINs ;)

Regards

Advertisements

One thought on “Re: FK, CHECK, ENUM or DOMAIN

  1. and you can also use enum in domain, which is my favorite 🙂
    Unlike author of orginal post, I like using enums whenever the set of values is quite stable. After all, if not – we could jsut always do ALTER TABLE .. TYPE .. USING .

    It is a shame still, that create type doesn’t work well with domains in postgres (default value isn’t set).

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s