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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Michael Cunningham
Sent: Monday, June 27, 2016 2:44 PM
To: oracle-l@freelists org <oracle-l@xxxxxxxxxxxxx>
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