{"id":619,"date":"2014-10-05T11:25:42","date_gmt":"2014-10-05T11:25:42","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=619"},"modified":"2014-10-05T11:25:42","modified_gmt":"2014-10-05T10:25:42","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=619","title":{"rendered":"Is it unique?"},"content":{"rendered":"<style type=\"text\/css\">\n.code, .code pre\n{\n  font-size: small;\n  color: black;\n  font-family: consolas, \"Courier New\", courier, monospace;\n  background-color: #ffffff;\n  \/*white-space: pre;*\/\n}\n.code pre { margin: 0em; }\n.code .rem { color: #ff0000; }\n.code .kwrd { color: #008080; }\n.code .str { color: #0000ff; }\n.code .op { color: #0000c0; }\n.code .preproc { color: #cc6633; }\n.code .asp { background-color: #ffff00; }\n.code .html { color: #800000; }\n.code .attr { color: #ff0000; }\n.code .alt\n{\n  background-color: #f4f4f4;\n  width: 100%;\n  margin: 0em;\n}\n.code .lnum { color: #606060; }<\/style>\n<p align=\"left\">The other day one of my colleagues said something like: &#8216;Hey, this is strange. <a title=\"Oracle\" href=\"http:\/\/www.oracle.com\/\" rel=\"nofollow\" target=\"_blank\">Oracle<\/a> is not working correctly&#8217;. My first thought is then: &#8216;well, you probably didn&#8217;t specify you requirements correctly&#8217;. What was the problem? A unique index was created on a table to implement a business rule which can be rephrased into &#8216;One-Manager-Per-Department&#8217;. This includes multiple columns where any combination is allowed, but only one manager is allowed per department.<\/p>\n<div align=\"left\">\n<!--more-->\n <\/div>\n<p align=\"left\">First, let&#8217;s make a simplified version of the well known EMP table.<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">TABLE<\/span> emp\r\n( empno  <span class=\"kwrd\">NUMBER<\/span>(4)\r\n, ename  <span class=\"kwrd\">VARCHAR2<\/span>(10)\r\n, job    <span class=\"kwrd\">VARCHAR2<\/span>(9)\r\n, deptno <span class=\"kwrd\">NUMBER<\/span>(2)\r\n)\r\n\/<\/pre>\n<\/div>\n<p align=\"left\">Then we try creating a unique index on the job column:<\/p>\n<p align=\"left\">\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">UNIQUE<\/span> <span class=\"kwrd\">INDEX<\/span> ui_one_manager <span class=\"kwrd\">ON<\/span> emp (job)\r\n\/<\/pre>\n<\/div>\n<p align=\"left\">Insert some data:<\/p>\n<p align=\"left\">\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7369<\/span>, <span class=\"str\">'SMITH'<\/span>,  <span class=\"str\">'CLERK'<\/span>,     <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7499<\/span>, <span class=\"str\">'ALLEN'<\/span>,  <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7521<\/span>, <span class=\"str\">'WARD'<\/span>,   <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);<\/pre>\n<\/div>\n<p>And notice that you cannot enter two employees with the same job, which is correct since we said the JOB should be unique. But we just want the MANAGER to be unique. I remembered that NULL values will not be included in the index so let&#8217;s try this: <\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">DROP<\/span> <span class=\"kwrd\">INDEX<\/span> ui_one_manager\r\n\/\r\n<span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">UNIQUE<\/span> <span class=\"kwrd\">INDEX<\/span> ui_one_manager <span class=\"kwrd\">ON<\/span> emp (<span class=\"kwrd\">CASE<\/span> job\r\n                                             <span class=\"kwrd\">WHEN<\/span> 'MANAGER' <span class=\"kwrd\">THEN<span> job\r\n                                             <span class=\"kwrd\">ELSE<\/span> <span class=\"kwrd\">NULL<\/span>\r\n                                           <span class=\"kwrd\">END<\/span>)\r\n\/<\/pre>\n<\/div>\n<p>Again insert some data: <\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7369<\/span>, <span class=\"str\">'SMITH'<\/span>,  <span class=\"str\">'CLERK'<\/span>,     <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7499<\/span>, <span class=\"str\">'ALLEN'<\/span>,  <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7521<\/span>, <span class=\"str\">'WARD'<\/span>,   <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7566<\/span>, <span class=\"str\">'JONES'<\/span>,  <span class=\"str\">'MANAGER'<\/span>,   <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7654<\/span>, <span class=\"str\">'MARTIN'<\/span>, <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7698<\/span>, <span class=\"str\">'BLAKE'<\/span>,  <span class=\"str\">'MANAGER'<\/span>,   <span class=\"op\">30<\/span>);<\/pre>\n<\/div>\n<p>Doing a lot better, but we cannot enter more than one MANAGER, even if they are in different departments. Let&#8217;s include the deptno in the columns for the index: <\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">DROP<\/span> <span class=\"kwrd\">INDEX<\/span> ui_one_manager\r\n\/\r\n<span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">UNIQUE<\/span> <span class=\"kwrd\">INDEX<\/span> ui_one_manager <span class=\"kwrd\">ON<\/span> emp (deptno\r\n                                          , <span class=\"kwrd\">CASE<\/span> job\r\n                                             <span class=\"kwrd\">WHEN<\/span> 'MANAGER' <span class=\"kwrd\">THEN<span> job\r\n                                             <span class=\"kwrd\">ELSE<\/span> <span class=\"kwrd\">NULL<\/span>\r\n                                           <span class=\"kwrd\">END<\/span>\r\n                                          )\r\n\/<\/pre>\n<\/div>\n<p>And try some data again:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7369<\/span>, <span class=\"str\">'SMITH'<\/span>,  <span class=\"str\">'CLERK'<\/span>,     <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7499<\/span>, <span class=\"str\">'ALLEN'<\/span>,  <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7521<\/span>, <span class=\"str\">'WARD'<\/span>,   <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);<\/pre>\n<\/div>\n<p>Pretty much back where we started. <\/p>\n<p>How about we apply the same case statement to the DEPTNO column like this:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">DROP<\/span> <span class=\"kwrd\">INDEX<\/span> ui_one_manager\r\n\/\r\n<span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">UNIQUE<\/span> <span class=\"kwrd\">INDEX<\/span> ui_one_manager <span class=\"kwrd\">ON<\/span> emp (<span class=\"kwrd\">CASE<\/span> job\r\n                                             <span class=\"kwrd\">WHEN<\/span> 'MANAGER' <span class=\"kwrd\">THEN<span> deptno\r\n                                             <span class=\"kwrd\">ELSE<\/span> <span class=\"kwrd\">NULL<\/span>\r\n                                           <span class=\"kwrd\">END<\/span>\r\n                                          , <span class=\"kwrd\">CASE<\/span> job\r\n                                             <span class=\"kwrd\">WHEN<\/span> 'MANAGER' <span class=\"kwrd\">THEN<span> job\r\n                                             <span class=\"kwrd\">ELSE<\/span> <span class=\"kwrd\">NULL<\/span>\r\n                                           <span class=\"kwrd\">END<\/span>\r\n                                          )\r\n\/<\/pre>\n<\/div>\n<p>And try some data again: <\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7369<\/span>, <span class=\"str\">'SMITH'<\/span>,  <span class=\"str\">'CLERK'<\/span>,     <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7499<\/span>, <span class=\"str\">'ALLEN'<\/span>,  <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7521<\/span>, <span class=\"str\">'WARD'<\/span>,   <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7566<\/span>, <span class=\"str\">'JONES'<\/span>,  <span class=\"str\">'MANAGER'<\/span>,   <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7654<\/span>, <span class=\"str\">'MARTIN'<\/span>, <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7698<\/span>, <span class=\"str\">'BLAKE'<\/span>,  <span class=\"str\">'MANAGER'<\/span>,   <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7782<\/span>, <span class=\"str\">'CLARK'<\/span>,  <span class=\"str\">'MANAGER'<\/span>,   <span class=\"op\">10<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7788<\/span>, <span class=\"str\">'SCOTT'<\/span>,  <span class=\"str\">'ANALYST'<\/span>,   <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7839<\/span>, <span class=\"str\">'KING'<\/span>,   <span class=\"str\">'PRESIDENT'<\/span>, <span class=\"op\">10<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7844<\/span>, <span class=\"str\">'TURNER'<\/span>, <span class=\"str\">'SALESMAN'<\/span>,  <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7876<\/span>, <span class=\"str\">'ADAMS'<\/span>,  <span class=\"str\">'CLERK'<\/span>,     <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7900<\/span>, <span class=\"str\">'JAMES'<\/span>,  <span class=\"str\">'CLERK'<\/span>,     <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7902<\/span>, <span class=\"str\">'FORD'<\/span>,   <span class=\"str\">'ANALYST'<\/span>,   <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">7934<\/span>, <span class=\"str\">'MILLER'<\/span>, <span class=\"str\">'CLERK'<\/span>,     <span class=\"op\">10<\/span>);<br \/>And this time it worked. <\/pre>\n<\/div>\n<p align=\"left\">To check if the unique index really works let&#8217;s try some more inserts:<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">2912<\/span>, <span class=\"str\">'BAREL'<\/span>, <span class=\"str\">'MANAGER'<\/span>, <span class=\"op\">10<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">2912<\/span>, <span class=\"str\">'BAREL'<\/span>, <span class=\"str\">'MANAGER'<\/span>, <span class=\"op\">20<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">2912<\/span>, <span class=\"str\">'BAREL'<\/span>, <span class=\"str\">'MANAGER'<\/span>, <span class=\"op\">30<\/span>);\r\n<span class=\"kwrd\">INSERT<\/span> <span class=\"kwrd\">INTO<\/span> EMP (EMPNO, ENAME, JOB, DEPTNO) <span class=\"kwrd\">VALUES<\/span> (<span class=\"op\">2912<\/span>, <span class=\"str\">'BAREL'<\/span>, <span class=\"str\">'MANAGER'<\/span>, <span class=\"op\">40<\/span>);<\/pre>\n<\/div>\n<p><\/p>\n<p align=\"left\">Only the last one works. Which is what was expected.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The other day one of my colleagues said something like: &#8216;Hey, this is strange. Oracle is not working correctly&#8217;. My first thought is then: &#8216;well, you probably didn&#8217;t specify you requirements correctly&#8217;. What was the problem? A unique index was created on a table to implement a business rule which can be rephrased into &#8216;One-Manager-Per-Department&#8217;. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,9],"tags":[],"class_list":["post-619","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/619","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=619"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/619\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=619"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}