OraFAQ Forum: SQL & PL/SQL » NOVALIDATE Constraint Using Index

Home » SQL & PL/SQL » SQL & PL/SQL » NOVALIDATE Constraint Using Index (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NOVALIDATE Constraint Using Index [message #629724]

Mon, 15 December 2014 11:53

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

Hi,
Please advice on below test case

create table TEST_A ( TXN_ID Number,txn_details varchar2(35),txn_date date );
create unique index pk_txn_ind on test_a(txn_id);
alter table test_a add (constraint pk_txn_ind Primary key (txn_id) using index pk_txn_ind);

Created a table, added Unique index, and adds a PK constraint,
it's an existing table, and has 100 mill rows, all good so far and we know there are no duplicates for PK column.

for some reason i have to

alter table test_a drop constraint pk_txn_ind;
drop index pk_txn_ind;
create unique index pk_txn_ind on test_a(txn_id);

--> This all works drops and recreate the index.
now trying to bring the PK constraint and as we know there are no Duplicates, was trying to use this

alter table test_a add (constraint pk_txn_ind Primary key(txn_id) NOVALIDATE using Index pk_txn_ind );
Error at line 1
ORA-00907: missing right parenthesis

And if remove Novalidate, it works

alter table test_a add (constraint pk_txn_ind Primary key(txn_id)  using Index pk_txn_ind  ) ;
Table altered.

was trying to speed the constraint creation for all prior data, i think novalidate will not validate existing data correct for future records it will validate.

is it not allowed to use novalidate there with using index clause ?
Please suggest.

Thanks .

Report message to a moderator

Re: NOVALIDATE Constraint Using Index [message #629725 is a reply to message #629724]

Mon, 15 December 2014 12:15

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> create table TEST_A ( TXN_ID Number,txn_details varchar2(35),txn_date date );

Table created.

SQL> create unique index pk_txn_ind on test_a(txn_id);

Index created.

SQL> alter table test_a add (constraint pk_txn_ind Primary key(txn_id) NOVALIDATE using Index pk_txn_ind );
alter table test_a add (constraint pk_txn_ind Primary key(txn_id) NOVALIDATE using Index pk_txn_ind )
                                                                             *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL> alter table test_a add (constraint pk_txn_ind Primary key(txn_id) using Index pk_txn_ind NOVALIDATE);

Table altered.

Report message to a moderator

Re: NOVALIDATE Constraint Using Index [message #629728 is a reply to message #629725]

Mon, 15 December 2014 12:31

azeem87
Messages: 116
Registered: September 2005
Location: dallas

Senior Member

Thank You Michel.

Report message to a moderator

Previous Topic:	Updating a column in many tables
Next Topic:	Using "&" in the password is recommended ??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 05:37:16 CDT 2024