Home » SQL & PL/SQL » SQL & PL/SQL » Creating Index while Adding Constraint (9i Rel 2)
Creating Index while Adding Constraint [message #277793] Wed, 31 October 2007 11:13 Go to next message
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 Go to previous messageGo to next message
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.
Re: Creating Index while Adding Constraint [message #277796 is a reply to message #277795] Wed, 31 October 2007 11:47 Go to previous message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Great,

thanks for Prompt Response,
So the rule is we can't use Unique Index on deferrable Constraint,that was the problem,

It works without unique Index

thanks
Previous Topic: Select rows depending on when they where create
Next Topic: printing a variable larger than 225
Goto Forum:
  


Current Time: Sat Feb 15 08:09:13 CST 2025