RE: Question about unique constraint

From: Tefft, Michael J <Michael.J.Tefft_at_snapon.com>
Date: Tue, 28 Jun 2016 19:36:08 +0000
Message-ID: <8CA507E7F87805479C5C3DF54AA713A780B96BA2_at_LISL-XMBS-13-PP.snaponglobal.com>

I believe you will need to create your primary-key constraint using a non-unique index. It is physically impossible for a unique index to tolerate duplicates, so it can not be used for a non-validated unique or primary-key constraint.

Example:
15:21:18 SQL> create table junk4 as select * from user_objects;

Table created.

15:21:23 SQL> select count(*), count(distinct object_name) from junk4;

  COUNT(*) COUNT(DISTINCTOBJECT_NAME)

---------- --------------------------
       522                        190

1 row selected.

15:21:43 SQL> create index junk4_idx on junk4(object_name);

Index created.

15:22:11 SQL> alter table junk4 add constraint junk4_pk primary key (object_name) novalidate;

Table altered.

So you have a non-validated primary-key constraint, supported by the non-unique index. It is already preventing new duplicates: 15:26:28 SQL> insert into junk4 select * from junk4 ; insert into junk4 select * from junk4
*

ERROR at line 1:
ORA-00001: unique constraint (NJ3417.JUNK4_PK) violated

But the original duplicates are still there: 15:22:57 SQL> alter table junk4 modify constraint junk4_pk validate; alter table junk4 modify constraint junk4_pk validate
*

ERROR at line 1:
ORA-02437: cannot validate (NJ3417.JUNK4_PK) - primary key violated

If we had tried using a unique index, we would not have gotten far: 15:25:28 SQL> create table junk5 as select * from junk4;

Table created.

15:25:52 SQL> create unique index junk5_idx on junk5(object_name); create unique index junk5_idx on junk5(object_name)

                                 *

ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

So, for your example, you would want something like this: CREATE INDEX dba6975_test_ IDX1 ON dba6975_test(test_id) LOGGING TABLESPACE INDXTBS1 ONLINE; alter table dba6975_test ADD CONSTRAINT dba6975_test_CPK primary key(test_id) using index dba6975_test_IDX1 novalidate; Mike Tefft

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Cunningham Sent: Monday, June 27, 2016 2:44 PM
To: oracle-l_at_freelists org <oracle-l_at_freelists.org> Subject: Question about unique constraint

Posting this for a colleague.

Hello everyone.

    I have various tables of fairly large sizes from 100GB till 3TB that I need to add primary key constraint in such way that clients can continue modifying tables. Locking tables is not an option. One of the approach I am considering is to build unique index online first then add constraints using index novalidate. Will this approach work? Are there side effects of adding constraints using novalidate option.

CREATE UNIQUE INDEX dba6975_test_PK ON dba6975_test(test_id) LOGGING TABLESPACE INDXTBS1 ONLINE;

alter table dba6975_test ADD CONSTRAINT dba6975_test_CPK primary key(test_id) using index dba6975_test_IDX1 novalidate

Suggestions are greatly appreciated.

--
Michael Cunningham

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 28 2016 - 21:36:08 CEST