tag:blogger.com,1999:blog-5124641802818980374.post4677454667357952457..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: Primary key / unique constraints enforced using a non-unique index - 11.1.0.6 and 11.1.0.7Unknownnoreply@blogger.comBlogger3125tag:blogger.com,1999:blog-5124641802818980374.post-80089559296834263762013-05-15T09:58:14.678+02:002013-05-15T09:58:14.678+02:00It is a common and good practice to use a non-uniq...It is a common and good practice to use a non-unique index to support a unique constraint. In this way the constraint can be dropped and re-created (quite an easy process) without the need to rebuild the index (quite a long-winded process). - ElwynAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-40610370752115304522012-03-10T23:56:59.157+01:002012-03-10T23:56:59.157+01:00Hi Goran,
Deferred or novalidated, but enabled co...Hi Goran,<br /><br />Deferred or novalidated, but enabled constraints are such examples:<br /><br />1. Oracle supports deferred constraints that can be violated during the transaction but need to be adhered to when committing the transaction. This is only possible with a non-unique index in case of a unique/primary key constraint because otherwise the unique index will not allow the temporary duplicates.<br /><br />2. It is probably not a common scenario, but in theory you can enable a unique / primary key constraint but leave it in NOVALIDATE state. If there are duplicates in the existing data, this is only possible using a non-unique index<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-66643706680315525492012-03-10T18:38:57.809+01:002012-03-10T18:38:57.809+01:00Hi Randolf,
nice blog ... although I have one ...Hi Randolf,<br /><br />nice blog ... although I have one 'beginners' question: why someone will enforce primary key or unique constraint using a non-unique index?<br />No example is comming to my mind where this could be applied.<br /><br />thanks,<br />gorangorannoreply@blogger.com