If you could create an issue in the tracker, that would be great. Make
sure to include in it that you would be interested in testing a fixed
version and we'll get you a snapshot to try once it is resolved.
Nathan
-----Original Message-----
From: liquibase-user-***@lists.sourceforge.net
[mailto:liquibase-user-***@lists.sourceforge.net] On Behalf Of Alan
Savage
Sent: Thursday, August 21, 2008 5:12 PM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] dropUniqueConstraint does not drop
constraint index on Oracle 10g
After gaining more experience with this problem, I believe the
suggestion for
LiquiBase to always instruct Oracle to drop the index when dropping a
primary key or unique constraint is the way to go (no "drop index"
property
on the XML command).
The scenario we have been running into:
- developer uses <dropPrimaryKey> paired with <addPrimaryKey>, or
<dropUniqueConstraint> paired with <addUniqueConstraint> to change a
constraint definition
- developer applies change to local database; no problems found.
- QA applies change to a test database; <addPrimaryKey> or
<addUniqueConstraint> fails because of "index already exists" Oracle
error.
We discovered the QA database was imported. When Oracle imports a
database,
the index is created separately from the constraint. Hence Oracle did
not
drop the index when dropping the constraint.
Right now, developers are working around the problem by coding Oracle
specific ALTER TABLE foo DROP PRIMARY KEY|CONSTRAINT foo DROP INDEX sql
statements, but I prefer they use the XML commands instead (we also need
to
support SQL Server).
Therefore, can LiquiBase include the "DROP INDEX" attribute when
dropping a
primary key or unique constraint for an Oracle database? DROP INDEX
works on
both Oracle 9i and 10g.
Let me know if I should open a change ticket, or if I can be of any help
(testing the proposed change for example).
- Alan
Post by Yasuo HONDAHi,
I think it depends on how primary key constraints are created.
This test does not use LiquiBase at all ;-)
If my guess is correct for this issue,
I do not think it is good to add dropIndex properties.
a) Primary key constraints generates constraint and index.
--> If you drop primary key constraint , constraints and index are dropped.
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));
alter table emp drop constraint pk_emp;
b) Index already created, then PK is added.
--> If you drop primary key constraint,
only constraints is dropped and index(pk_emp3_ind) remains.
CREATE TABLE EMP3
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));
create unique index pk_emp3_ind on emp3 (empno);
alter table emp3 add constraint pk_emp3 primary key (empno);
alter table emp3 drop constraint pk_emp3;
--
Yasuo HONDA
On Thu, Jul 24, 2008 at 2:25 PM, Voxland, Nathan
Post by Voxland, NathanIt would probably make sense to add, unless it would be better to just
assume that droping unique constraints means you want to drop the
associated index as well. Any opinions?
Nathan
________________________________
Sent: Wed 7/23/2008 8:57 PM
Subject: [Liquibase-user] dropUniqueConstraint does not drop
constraint
Post by Yasuo HONDAPost by Voxland, Nathanindex on Oracle 10g
Hello,
Recently we ran into a problem with Oracle 10g and the
dropUniqueConstraint
command. The dropUniqueConstraint command dropped the constraint, but the
index backing the constraint remained. Oracle requires the "DROP INDEX"
attribute on the ALTER TABLE DDL statement to ensure the index is dropped
along with the constraint. We didn't discover the problem until we
tried
Post by Yasuo HONDAPost by Voxland, Nathanto
replace the constraint, which failed because the index from the previous
definition was still present.
I'm wondering if it would make sense to add a "dropIndex =
true|false"
Post by Yasuo HONDAPost by Voxland, Nathanattribute to the dropUniqueConstraint command? I guess this is
specific
Post by Yasuo HONDAPost by Voxland, Nathanto
Oracle, but I imagine others using Oracle are going to run into the same
problem.
Thanks,
Alan
...
--
View this message in context:
http://www.nabble.com/dropUniqueConstraint-does-not-drop-constraint-inde
x-on-Oracle-10g-tp18624090p19097955.html
Sent from the LiquiBase - User mailing list archive at Nabble.com.
------------------------------------------------------------------------
-
This SF.Net email is sponsored by the Moblin Your Move Developer's
challenge
Build the coolest Linux based applications with Moblin SDK & win great
prizes
Grand prize is a trip for two to an Open Source event anywhere in the
world
http://moblin-contest.org/redirect.php?banner_id=100&url=/