Creating Index while Adding Constraint [message #277793] |
Wed, 31 October 2007 11:13  |
faiz_hyd
Messages: 294 Registered: February 2006
|
Senior Member |
|
|
Hi,
I am trying to create a unique initially deferrable constraint and along with that a Unique index also
SQL> ALTER TABLE CUST_Rule ADD (
2 CONSTRAINT XAK_cust
3 UNIQUE (CUST_ID, SEQ_NB)
4 DEFERRABLE INITIALLY DEFERRED USING INDEX ( create unique index XAK_ON CUST_RULE (CUST_ID, SEQ_NB) tablespace TS_INDX));
ALTER TABLE CUST_RULE ADD (
*
ERROR at line 1:
ORA-01418: specified index does not exist
The reason i want to merge index creation with alter table so that the unique index gets created in the specided Tablespace,
where as if i removed the index creation it works,
SQL> ALTER TABLE CUST_RULE ADD (
2 CONSTRAINT XAK_CUST
3 UNIQUE (CUST_ID, SEQ_NB)
4 DEFERRABLE INITIALLY DEFERRED );
Table altered.
SQL> \
Any idea what is wrong in my First Statement.
Thanks
[Updated on: Wed, 31 October 2007 11:21] Report message to a moderator
|
|
|
Re: Creating Index while Adding Constraint [message #277795 is a reply to message #277793] |
Wed, 31 October 2007 11:37   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
couple of things
1. Get rid of the underscore between XAK_ON
2. Yopu cannot use a unique index if you want a defferrable constraint:
SQL> create table copy_emp as select * from employees;
Table created.
SQL> ALTER TABLE copy_emp ADD (
2 CONSTRAINT XAK_cust
3 UNIQUE (last_name, first_name)
4 DEFERRABLE INITIALLY DEFERRED USING INDEX ( create unique index XAK ON copy_emp (last_name, first_name) tablespace users));
ALTER TABLE copy_emp ADD (
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.
SQL> ALTER TABLE copy_emp ADD (
2 CONSTRAINT XAK_cust
3 UNIQUE (last_name, first_name)
4 DEFERRABLE INITIALLY DEFERRED USING INDEX ( create index XAK ON copy_emp (last_name, first_name) tablespace users));
Table altered.
|
|
|
|