{"id":6,"date":"2014-03-12T14:46:00","date_gmt":"2014-03-12T14:46:00","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/2014\/03\/12\/primary-key-not-unique\/"},"modified":"2015-11-17T20:27:23","modified_gmt":"2015-11-17T19:27:23","slug":"primary-key-not-unique","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2014\/03\/12\/primary-key-not-unique\/","title":{"rendered":"Primary key not unique"},"content":{"rendered":"<p>Today I discovered that primary key indexes do not need to be unique. This makes a mess for checking supplemental logging for GoldenGate.<\/p>\n<p>Here is the example:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSQL&gt; create table t(c1 number, c2 number);\r\nTable created.\r\n\r\n\r\nSQL&gt; create index i on t(c1,c2);\r\nIndex created.\r\n\r\n\r\nSQL&gt; alter table t add primary key (c1,c2) using index i;\r\nTable altered.\r\n\r\n\r\nSQL&gt; select uniqueness from dba_indexes where table_name=\u2019T';\r\nUNIQUENES\r\n\u2014\u2014\u2014\r\nNONUNIQUE\r\n\r\n\r\nSQL&gt;  select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where table_name=\u2019T';\r\nCONSTRAINT_NAME                C\r\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014 -\r\nSYS_C001632047                 P\r\n\r\n\r\nSQL&gt; insert into t values (1,2);\r\n1 row created.\r\n\r\n\r\nSQL&gt; commit;\r\nCommit complete.\r\n\r\n\r\nSQL&gt; insert into t values (1,2);\r\ninsert into t values (1,2)\r\n*\r\nERROR at line 1:\r\nORA-00001: unique constraint (SYS.SYS_C001632047) violated\r\n\r\n\r\nSQL&gt; alter table t drop constraint SYS_C001632047;\r\nTable altered.\r\n\r\n\r\nSQL&gt; insert into t values (1,2);\r\n1 row created.\r\n\r\n\r\nSQL&gt; commit;\r\nCommit complete.\r\n\r\n\r\nSQL&gt; alter table t add primary key (c1,c2) using index i;\r\nalter table t add primary key (c1,c2) using index i\r\n*\r\nERROR at line 1:\r\nORA-02437: cannot validate (SYS.SYS_C001632048) \u2013 primary key violated\r\n\r\n\r\nSQL&gt; alter table t add primary key (c1,c2) using index i novalidate;\r\nTable altered.\r\n\r\n\r\nSQL&gt;  select uniqueness from dba_indexes where table_name=\u2019T';\r\nUNIQUENES\r\n\u2014\u2014\u2014\r\nNONUNIQUE\r\n\r\n\r\nSQL&gt; select * from t;\r\n        C1         C2\r\n\u2014\u2014\u2014- \u2014\u2014\u2014-\r\n         1          2\r\n         1          2\r\n\r\n\r\nSQL&gt; select VALIDATED from dba_constraints where table_name=\u2019T';\r\nVALIDATED\r\n\u2014\u2014\u2014\u2014-\r\nNOT VALIDATED\r\n\r\n\r\nSQL&gt; insert into t values (1,2);\r\ninsert into t values (1,2)\r\n*\r\nERROR at line 1:\r\nORA-00001: unique constraint (SYS.SYS_C001632049) violated\r\n\r\n\r\nSQL&gt; drop table t;\r\nTable dropped.\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Today I discovered that primary key indexes do not need to be unique. This makes a mess for checking supplemental logging for GoldenGate. Here is the example: SQL&gt; create table t(c1 number, c2 number); Table created. SQL&gt; create index i on t(c1,c2); Index created. SQL&gt; alter table t add primary key (c1,c2) using index i; [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6,2],"tags":[],"class_list":{"0":"post-6","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-goldengate","7":"category-oracle","8":"category-oracle-11g","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/6","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=6"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/6\/revisions"}],"predecessor-version":[{"id":89,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/6\/revisions\/89"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=6"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=6"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=6"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}