Unique Constraint With DEFERRABLE INITIALLY DEFERRED NOVALIDATE [message #655710] |
Fri, 09 September 2016 08:45 |
|
fixxxer
Messages: 45 Registered: August 2014
|
Member |
|
|
I am adding a unique constraint on a table with existing data which violates the unique constraint. New data being inserted will comply with the unique constraint, so I am creating it using:
ALTER TABLE my_table ADD CONSTRAINT my_table_ux1 UNIQUE (col1) DEFERRABLE INITIALLY DEFERRED NOVALIDATE;
This is working fine, but the index behind the unique constraint is being created in the default tablespace.
I couldn't see the syntax for specifying the tablespace to be used for the index.
Is this possible to do?
Thanks in advance - FIXXXER
|
|
|
|
|
|
|
|
|
|
|
Re: Unique Constraint With DEFERRABLE INITIALLY DEFERRED NOVALIDATE [message #655719 is a reply to message #655718] |
Fri, 09 September 2016 11:39 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I was not suggesting that you use rebuild as a matter of course, I was saying that in this case where they already have an existing index simply use rebuild to move it to the correct tablespace instead of dropping and rebuilding the index in the correct location. That being said, you should always create the non unique index in the correct tablespace and enforce it with the DEFERRABLE INITIALLY DEFERRED NOVALIDATE constraint.
|
|
|
|
|
Re: Unique Constraint With DEFERRABLE INITIALLY DEFERRED NOVALIDATE [message #655731 is a reply to message #655715] |
Fri, 09 September 2016 21:49 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 09 September 2016 11:47[code]
You can't even not workaroond the problem creating the constraint in DISABLE state to then ENABOE NOVALIDATE it because DISABLE leads to the same error:
Incorrect sequence of clauses again. ENABLE/DISABLE, same as VALIDATE/NOVALIDATE must follow USING clause while DEFERRABLE INITIALLY DEFERRED must precede it:
SQL> alter table t add constraint t_unq unique(val)
2 DEFERRABLE INITIALLY DEFERRED
3 using index (create index t_unq on t(val) tablespace users)
4 DISABLE
5 /
Table altered.
SQL>
It is all in syntax diagram:
[ [ [ NOT ] DEFERRABLE ]
[ INITIALLY { IMMEDIATE | DEFERRED } ]
| [ INITIALLY { IMMEDIATE | DEFERRED } ]
[ [ NOT ] DEFERRABLE ]
]
[ RELY | NORELY ]
[ using_index_clause ]
[ ENABLE | DISABLE ]
[ VALIDATE | NOVALIDATE ]
[ exceptions_clause ]
SY.
|
|
|
|
|
|
|
|