cryptic error in dropping the Primary Key
Rahul Babbar
I am having the following scenario...
I have a primary key on a column in a table.
I want to create that column a unique key(and not the primary key)
I plan to add another column later, which i'll make as the Primary
Key.
I issue the following set of commands.
Statement 1. Alter table Table1 drop primary key
Statement 2. Alter table Table1 add constraint UK_Table1
unique(column)
the 2nd statement gives the error saying,
SQL0598W Existing index "<INDEX>" is used as the index for
the primary key or a unique key. SQLSTATE=01550
We figured out that first we had to drop the index(related to the
primary key) explicity, before adding the unique key, by using
Statement 3. Drop Index <pk_table1>
After this statement, the statement "Alter table Table1 add constraint
UK_Table1 unique(column)" works fine if it were executed in that
order.
that means
Scenario 1
If we execute the statements as
Statement1--> Statement3 --> Statement2, then and only then the unique
index is created.
Scenario 2
However, if we execute the statements as
Statement1--> Statement2 --> Statement3, then we can never get to the
desired state of dropping the primary key succesfully and recreating
it as Unique Key.
In scenario 2,
the statement 2 gives the error saying
"SQL0598W Existing index "<INDEX>" is used as the index for
the primary key or a unique key. SQLSTATE=01550"
and the statement3 gives error saying,
" SQL0669N A system required index cannot be dropped
explicitly.....use the alter table command "
and when we try to drop the constraint(in fact, an index), it says
that
the constraint doesn't exist in the first place.
However, we can see the index <pk_Table1> in the syscat.indexes.
So, once we go to scenario 2, we just cannot drop the primary key and
create it as unique.
Please tell what should be done to fix the error coming
" SQL0669N A system required index cannot be dropped
explicitly.....use the alter table command "
and what should be done to drop the primary key once we have already
got into scenario2.
P.S. The thing is pretty cryptic and i have tried to explain whatever
i could get and find out in various scenarios. Please take some time
to understand and tell the solution..
Thanks a lot..
Rahul
Serge Rielau
>the primary key or a unique key. SQLSTATE=01550
Many customers consider it good practise to explicitly create all
indexes and have UNIQUE and PRIMARY KEY constraints re-use these indexes.
The WARNING above informs them that thid actually happened.
The benefit is two fold:
1. You get "nice" names that you picked instead of SQL*****
2. You can use INCLUDE columns
Now, no matter whether you define the index yourself or let DB2 define
the index for you, you cannot drop an index that DB2 requires to enforce
integrity of the database.
Hope that clears things up a bit
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Knut Stolze
> Hi,
>
> I am having the following scenario...
>
> I have a primary key on a column in a table.
> I want to create that column a unique key(and not the primary key)
> I plan to add another column later, which i'll make as the Primary
> Key.
I'm curious: why do you want to do that? A primary key is the same as a
unique key - except that it is named "primary key".
> and when we try to drop the constraint(in fact, an index), it says
A constraint is not an index. In theory, an index can be created or dropped
without any influence on the application (except performance-wise). An
exception to that are "unique" indexes.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Rahul Babbar
Hi,
We want to make the existing Primary key as Unique key....because we
want to have a new primary key for the table, which will follow
certain trend, like table_id and will not be combination of two
columns, etc...so we want to create the current primary key as the
unique key and then add the new column table_id and then declare it as
PK.
Further, i was trying to drop the constraint(in fact, an index)
because the error
" SQL0669N A system required index cannot be dropped
explicitly.....use the alter table command "
stated that, if the index was created by the constraint, use the alter
table command.
Rahul
Knut Stolze
> On Nov 7, 12:25 am, Knut Stolze <sto...@de.ibm.com> wrote:
>> Rahul Babbar wrote:
>> > Hi,
>>
>> > I am having the following scenario...
>>
>> > I have a primary key on a column in a table.
>> > I want to create that column a unique key(and not the primary key)
>> > I plan to add another column later, which i'll make as the Primary
>> > Key.
>>
>> I'm curious: why do you want to do that? A primary key is the same as a
>> unique key - except that it is named "primary key".
>>
>> > and when we try to drop the constraint(in fact, an index), it says
>>
>> A constraint is not an index. In theory, an index can be created or
>> dropped
>> without any influence on the application (except performance-wise). An
>> exception to that are "unique" indexes.
>>
> We want to make the existing Primary key as Unique key....because we
> want to have a new primary key for the table, which will follow
> certain trend, like table_id and will not be combination of two
> columns, etc...so we want to create the current primary key as the
> unique key and then add the new column table_id and then declare it as
> PK.
So you don't have any technical reason for that change but only coding
standards. I can understand that. ;-)
> Further, i was trying to drop the constraint(in fact, an index)
My point is that a constraint is NOT an index. Both are two different
beasts. So when you say you want to drop a constraint, it has nothing to
do with indexes. The only relationship between unique constraints and
indexes is that DB2 implicitly creates a unique index to support a unique
constraint.
> because the error
> " SQL0669N A system required index cannot be dropped
> explicitly.....use the alter table command "
> stated that, if the index was created by the constraint, use the alter
> table command.
Yes. The supporting index is needed to enforce a unique constraint. Hence,
you cannot drop the index only (without removing the constraint) because
DB2 would not be able to enforce the constraint any longer.
p.s: I think the key issue is not to mix different concepts, i.e. index and
unique constraints.
Serge Rielau
> We want to make the existing Primary key as Unique key....because we
> want to have a new primary key for the table, which will follow
> certain trend, like table_id and will not be combination of two
> columns, etc...so we want to create the current primary key as the
> unique key and then add the new column table_id and then declare it as
> PK.
wide composite PK's doesn't mean all composite PK's are bad. It's called
"Throwing the child out with the bathwater"....
Cheers
Rahul Babbar
Ok...
I didn't decide it...that the PK should be table_id column...
Somebody else decided it and it didn't seem a bad idea....
May be i don't know if they had some other reason to make table_id as
the PK...and make existing pk as the UKs....
But what i know is that these errors come when i try to drop the
primary key....
I never say...composite keys are bad....but that it gives errors as i
tried to explain above...
Thanks
Rahul
Serge Rielau
> I never say...composite keys are bad....but that it gives errors as i
> tried to explain above...
Rahul Babbar
All the discussions in this group are very helpful...
even if one doesn't get the exact solution, one gets pretty close to
it. :-)
Thanks a lot
Rahul
Arun Srinivasan
If you drop the primary key, then in now way should you see the
system index meant for that primary key. May be the index you are
trying to drop in statement 2 is for another constraint (probably
unique key), but when I drop a primary key, I see a sqlxx index
dropped. Please check that.
Also, you are getting the error that 'you cannot drop a system index'
since the index is being used currently for enforcing a constraint,
which wont be primary key, because you just dropped it. Did you do a
commit work after the primary key drop?
db2 creates indexes to enforce constraints, primary keys will be
indexed on them columns, and not null will be enforced, while in
unique keys I believe not nulls are allowed, but only one row can have
null on both column, since the
second row with all nulls will be a duplicate and result in non-
uniqueness, correct me if I am wrong guys..
Thus I can say that these errors are not cryptic if you take a step
back and try to understand what db2 is doing.
Bye
Arun
Knut Stolze
> Hi Rahul
> If you drop the primary key, then in now way should you see the
> system index meant for that primary key. May be the index you are
> trying to drop in statement 2 is for another constraint (probably
> unique key), but when I drop a primary key, I see a sqlxx index
> dropped. Please check that.
...unless the index supporting the PK was created explicitly before the PK
was added to the table.
> db2 creates indexes to enforce constraints, primary keys will be
> indexed on them columns, and not null will be enforced, while in
> unique keys I believe not nulls are allowed
Unique indexes allow a single NULL. Unique constraints don't - and an index
is not a constraint.
Arun Srinivasan
Yes, unique indexes dont allow null since they can be created only on
a not null column, but I am interested
about the difference between '' and null, in case of varchars, and
chars...is there any?
Thanks
Arun
Serge Rielau
> but I am interested
> about the difference between '' and null, in case of varchars, and
> chars...is there any?
In DB2 and the SQL Standard an empty string ('') is a completely
different animal than a NULL.
An empty string is a value. It is the neutral element for CONCAT, just
like 0 is the neutral element for +/-
NULL is the absence of a value.
Knut Stolze
> Yes, unique indexes dont allow null since they can be created only on
> a not null column, but I am interested
It's the other way around: Unique indexes allow NULLs - you can create them
on nullable columns. You cannot create unique constraints on nullable
columns.
> about the difference between '' and null, in case of varchars, and
> chars...is there any?
'' is the empty string and, therefore, something completely different than
NULL. Compare NULL to a NULL pointer in C/C++ or a null reference in Java.
Those are also completely different from empty strings and should not be
confused.