{"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":29,"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 v27.2 (Yoast SEO v27.2) - 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=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\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\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/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":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","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":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_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\/29"}],"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}]}}