{"id":11325,"date":"2018-06-08T12:29:44","date_gmt":"2018-06-08T10:29:44","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/"},"modified":"2018-06-08T12:29:44","modified_gmt":"2018-06-08T10:29:44","slug":"do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/","title":{"rendered":"Do you need the same column with the same check constraint twice? Create a domain!"},"content":{"rendered":"<p>Did you know that you can create domains in PostgreSQL? No, nothing to worry about. We&#8217;ll take <a href=\"https:\/\/twitter.com\/FranckPachot\/status\/1004721623679881216\" target=\"_blank\" rel=\"noopener\"> Frank&#8217;s leave<\/a> for a new opportunity as a chance to introduce the concept of domains. @Franck: Yes, although we all fully understand your decision and the reasons to move on to a new challenge, this post is dedicated to you and you need to be the example in the following little demo. Lets go &#8230;<\/p>\n<p><!--more--><\/p>\n<p>For the (not in any way serious scope) of this post lets assume that we do not want Franck anymore to blog on our blog site. We want to ban him. Of course we could simply delete his user account or disable the login. But, hey, we want to do that by using a domain as that is much more fun to do. Lets assume our blog software comes with two little tables that look like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# d blogs\n                            Table \"public.blogs\"\n Column |  Type   | Collation | Nullable |              Default              \n--------+---------+-----------+----------+-----------------------------------\n id     | integer |           | not null | nextval('blogs_id_seq'::regclass)\n author | text    |           |          | \n blog   | text    |           |          | \nIndexes:\n    \"blogs_pk\" PRIMARY KEY, btree (id)\nReferenced by:\n    TABLE \"blog_comments\" CONSTRAINT \"comments_ref_blogs\" FOREIGN KEY (blog_id) REFERENCES blogs(id)\n\npostgres=# d blog_comments\n                             Table \"public.blog_comments\"\n Column  |  Type   | Collation | Nullable |                  Default                  \n---------+---------+-----------+----------+-------------------------------------------\n id      | integer |           | not null | nextval('blog_comments_id_seq'::regclass)\n blog_id | integer |           |          | \n author  | text    |           |          | \n comment | text    |           |          | \nIndexes:\n    \"blog_comments__pk\" PRIMARY KEY, btree (id)\nForeign-key constraints:\n    \"comments_ref_blogs\" FOREIGN KEY (blog_id) REFERENCES blogs(id)\n<\/pre>\n<p>When we want that Franck is not anymore able to create blogs and to comment on blogs we could do something like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# alter table blogs add constraint no_franck_blogs check ( author ~ '!^Franck' );\nALTER TABLE\npostgres=# alter table blog_comments add constraint no_franck_comments check ( author ~ '!^Franck' );\nALTER TABLE\n<\/pre>\n<p>This will prevent Franck (actually it will prevent all people called Franck, but this is good in that case as we do not like people called Franck anymore) from inserting anything into these two tables:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# insert into blogs (author,blog) values ('Franck Pachot','another great blog');\nERROR:  new row for relation \"blogs\" violates check constraint \"no_franck_blogs\"\nDETAIL:  Failing row contains (1, Franck Pachot, another great blog).\n<\/pre>\n<p>(Btw: Did you notice that you can use regular expressions in check constraints?)<\/p>\n<p>This works and does what we want it to do. But there is an easier way of doing it. Currently we need to maintain two check constraints which are doing the same thing. By creating a domain we can centralize that:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create domain no_franck_anymore as text check (value ~ '!^Franck' );\nCREATE DOMAIN\n<\/pre>\n<p>Once we have that we can use the domain in our tables:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# alter table blogs drop constraint no_franck_blogs;\nALTER TABLE\npostgres=# alter table blog_comments drop constraint no_franck_comments;\nALTER TABLE\npostgres=# alter table blogs alter column author type no_franck_anymore;\nALTER TABLE\npostgres=# alter table blog_comments alter column author type no_franck_anymore;\nALTER TABLE\npostgres=# d blogs\n                                 Table \"public.blogs\"\n Column |       Type        | Collation | Nullable |              Default              \n--------+-------------------+-----------+----------+-----------------------------------\n id     | integer           |           | not null | nextval('blogs_id_seq'::regclass)\n author | no_franck_anymore |           |          | \n blog   | text              |           |          | \nIndexes:\n    \"blogs_pk\" PRIMARY KEY, btree (id)\nReferenced by:\n    TABLE \"blog_comments\" CONSTRAINT \"comments_ref_blogs\" FOREIGN KEY (blog_id) REFERENCES blogs(id)\n\npostgres=# d blog_comments\n                                  Table \"public.blog_comments\"\n Column  |       Type        | Collation | Nullable |                  Default                  \n---------+-------------------+-----------+----------+-------------------------------------------\n id      | integer           |           | not null | nextval('blog_comments_id_seq'::regclass)\n blog_id | integer           |           |          | \n author  | no_franck_anymore |           |          | \n comment | text              |           |          | \nIndexes:\n    \"blog_comments__pk\" PRIMARY KEY, btree (id)\nForeign-key constraints:\n    \"comments_ref_blogs\" FOREIGN KEY (blog_id) REFERENCES blogs(id)\n<\/pre>\n<p>This still prevents Franck from blogging:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# insert into blogs (author,blog) values ('Franck Pachot','another great blog');\nERROR:  value for domain no_franck_anymore violates check constraint \"no_franck_anymore_check\"\n<\/pre>\n<p>&#8230; but we only need to maintain one domain and not two or more check constraints.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Did you know that you can create domains in PostgreSQL? No, nothing to worry about. We&#8217;ll take Frank&#8217;s leave for a new opportunity as a chance to introduce the concept of domains. @Franck: Yes, although we all fully understand your decision and the reasons to move on to a new challenge, this post is dedicated [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-11325","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Do you need the same column with the same check constraint twice? Create a domain! - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Do you need the same column with the same check constraint twice? Create a domain!\" \/>\n<meta property=\"og:description\" content=\"Did you know that you can create domains in PostgreSQL? No, nothing to worry about. We&#8217;ll take Frank&#8217;s leave for a new opportunity as a chance to introduce the concept of domains. @Franck: Yes, although we all fully understand your decision and the reasons to move on to a new challenge, this post is dedicated [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-08T10:29:44+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Do you need the same column with the same check constraint twice? Create a domain!\",\"datePublished\":\"2018-06-08T10:29:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\\\/\"},\"wordCount\":310,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\\\/\",\"name\":\"Do you need the same column with the same check constraint twice? Create a domain! - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-06-08T10:29:44+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Do you need the same column with the same check constraint twice? Create a domain!\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Do you need the same column with the same check constraint twice? Create a domain! - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/","og_locale":"en_US","og_type":"article","og_title":"Do you need the same column with the same check constraint twice? Create a domain!","og_description":"Did you know that you can create domains in PostgreSQL? No, nothing to worry about. We&#8217;ll take Frank&#8217;s leave for a new opportunity as a chance to introduce the concept of domains. @Franck: Yes, although we all fully understand your decision and the reasons to move on to a new challenge, this post is dedicated [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/","og_site_name":"dbi Blog","article_published_time":"2018-06-08T10:29:44+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Do you need the same column with the same check constraint twice? Create a domain!","datePublished":"2018-06-08T10:29:44+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/"},"wordCount":310,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/","url":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/","name":"Do you need the same column with the same check constraint twice? Create a domain! - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-06-08T10:29:44+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/do-you-need-the-same-column-with-the-same-check-constraint-twice-create-a-domain\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Do you need the same column with the same check constraint twice? Create a domain!"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11325"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11325\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11325"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11325"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}