Bug in Oracle

Toad for Oracle
1

Sir,i have another question.
Is this a bug in oracle or what,i don’t understand.

When i tried to alter a primary key column in a table,to NULL ,
first time sqlplus shows the message ,“Table altered”
but subsequently ,i got error,
ORA-01451: column to be modified to NULL cannot be modified to NULL

My question is that ,
i should get a consistent message every time.
Not different message at different time.So is this a bug in oracle?

I have gone through steps like this.

–>Suppose in a Employee table,EMP_ID column is primary key

–>I tried to alter EMP_ID column to accept NULL values,
but it generates error
ALTER TABLE EMPLOYEE
MODIFY (EMP_ID NULL);
ORA-01451: column to be modified to NULL cannot be modified to NULL

–>So i tried ,to alter EMP_ID column to NOT NULL,
and i got message ,Table altered.
ALTER TABLE EMPLOYEE
MODIFY (EMP_ID NOT NULL);

–>Then i tried to alter EMP_ID column to accept NULL values,
but THIS TIME IT DOES NOT GENERATES ANY ERROR,
and i got message ,Table altered.
ALTER TABLE EMPLOYEE
MODIFY (EMP_ID NULL);

But when again ,i run the same statement again,it generates error
ORA-01451: column to be modified to NULL cannot be modified to NULL

2

Message from: eklinger

I’m not sure what you are trying to accomplish but bottom line is that a
Primary Key can NOT be nullable. The very definition of a primary key does not
allow NULL keys.

If you are using a composite key as your primary key change that key to a Unique
Key which will allow NULLs. If you do that then I would still have a single
primary key that gets generated by a sequence.

Ed
[TeamT]

Historical Messages

Author: Ed Klinger
Date: Fri Jul 16 06:38:31 PDT 2010
I’m not sure what you are trying to accomplish but bottom line is that a
Primary Key can NOT be nullable. The very definition of a primary key does not
allow NULL keys.

If you are using a composite key as your primary key change that key to a Unique
Key which will allow NULLs. If you do that then I would still have a single
primary key that gets generated by a sequence.

Ed
[TeamT]

__

Author: jenasatyabrata82_718
Date: Thu Jul 15 19:02:38 PDT 2010
Sir,i have another question. Is this a bug in oracle or what,i don’t understand.
When i tried to alter a primary key column in a table,to NULL , first time
sqlplus shows the message ,“Table altered” but subsequently ,i got error,
ORA-01451: column to be modified to NULL cannot be modified to NULL My question
is that , i should get a consistent message every time. Not different message at
different time.So is this a bug in oracle? I have gone through steps like this.
–>Suppose in a Employee table,EMP_ID column is primary key -->I tried to alter
EMP_ID column to accept NULL values, but it generates error ALTER TABLE EMPLOYEE
MODIFY (EMP_ID NULL); ORA-01451: column to be modified to NULL cannot be
modified to NULL -->So i tried ,to alter EMP_ID column to NOT NULL, and i got
message ,Table altered. ALTER TABLE EMPLOYEE MODIFY (EMP_ID NOT NULL); -->Then i
tried to alter EMP_ID column to accept NULL values, but THIS TIME IT DOES NOT
GENERATES ANY ERROR, and i got message ,Table altered. ALTER TABLE EMPLOYEE
MODIFY (EMP_ID NULL); But when again ,i run the same statement again,it
generates error ORA-01451: column to be modified to NULL cannot be modified to
NULL
__

3

I’m not sure what you are trying to accomplish but bottom line is that a Primary Key can NOT be nullable. The very definition of a primary key does not allow NULL keys.

If you are using a composite key as your primary key change that key to a Unique Key which will allow NULLs. If you do that then I would still have a single primary key that gets generated by a sequence.

Ed
[TeamT]

4

Message from: jenasatyabrata82_718

Sir,i know it very clearly that ,primary key column ,can not be null. But the
bug in oracle is that, when first time ,i tried to ALTER the primary key column
in the table,to accept NULL, oracle shows the message that ,“TABLE ALTERED.” But
when i queried the USER_TAB_COLUMNS,data dictionary ,i found that the primary
column,is actually NOT ALTERED AT ALL,TO ACCEPT NULL VALUES. Although oracle is
enforcing the rule that,PRIMARY KEY COLUMN CAN NOT BE NULL ,but how ORACLE
GENERATED THE FALSE MESSAGE THAT -“Table Altered”. where as the DDL operation
was not successful.

5

Sir,i know it very clearly that ,primary key column ,can not be null.

But the bug in oracle is that,
when first time ,i tried to ALTER the primary key column in the table,to accept NULL,
oracle shows the message that ,“TABLE ALTERED.”
But when i queried the USER_TAB_COLUMNS,data dictionary ,i found that the primary column,is actually NOT ALTERED AT ALL,TO ACCEPT NULL VALUES.

Although oracle is enforcing the rule that,PRIMARY KEY COLUMN CAN NOT BE NULL ,but how ORACLE GENERATED THE FALSE MESSAGE THAT -“Table Altered”.
where as the DDL operation was not successful.

But when second time ,i tried to ALTER the primary key column,to accept NULL,
oracle shows the message that ,
ORA-01451: column to be modified to NULL cannot be modified to NULL

So what i am saying is that,why oracle show me the correct ,message ,
“ORA-01451: column to be modified to NULL cannot be modified to NULL”,
at the second time DDL operation ,rather than showing me at the first time DDL operation.

And how even if ,at the first time, the DLL operation was not successful,
how oracle shows the false message that “Table Altered”.

Please go through the steps as i have mentioned in the previous post ,to ALTER TABLE,then you can find the problem.

6

I’ve might of missed it but I don’t see you state what version of Oracle
you are using.

I tried to reproduce this in 11.2 and don’t see the issue you are
talking about. Maybe it is fixed in the latest version. I guess bottom
line is that it is not letting you change the column to be nullable
which would be a huge bug.

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> CREATE TABLE EMP
2 (EMP_ID NUMBER,
3 EMP_NAME VARCHAR2(1));

Table created.

SQL>
SQL> ALTER TABLE EMP ADD (
2 CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID));

Table altered.

SQL> SELECT column_name, nullable
2 FROM USER_TAB_COLUMNS
3 WHERE table_name = ‘EMP’
4 ORDER BY column_id;

COLUMN_NAME NULLABLE

7

I can more or less reproduce it.

Scenario:

system@DECP> create table tt(p1 number primary key);

Table created.

system@DECP> alter table tt modify p1 null;
alter table tt modify p1 null
*
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL

Okay, that's whta you would expect.

But now, do something senseless like:

system@DECP> alter table tt modify p1 not null;

Table altered.

and then:

system@DECP> alter table tt modify p1 null;

Table altered.

If you execute this last statement a second time you get the expected
error again.

I think I understand why it works as it does, but it is a bit
confusing. Perhaps Oracle should not allow the NOT NULL constraint on a
column that already is implicitly not nullable because of another
constraint.

Peter

Op 16-07-2010 17:52, Ed Klinger schreef:

I've might of missed it but I don't see you state what version of Oracle
you are using.

I tried to reproduce this in 11.2 and don't see the issue you are
talking about. Maybe it is fixed in the latest version. I guess bottom
line is that it is not letting you change the column to be nullable
which would be a huge bug.

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> CREATE TABLE EMP
2 (EMP_ID NUMBER,
3 EMP_NAME VARCHAR2(1));

Table created.

SQL>
SQL> ALTER TABLE EMP ADD (
2 CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID));

Table altered.

SQL> SELECT column_name, nullable
2 FROM USER_TAB_COLUMNS
3 WHERE table_name = 'EMP'
4 ORDER BY column_id;

COLUMN_NAME NULLABLE

8

Morning all,

system@DECP> create table tt(p1 number primary key);
Table created.

system@DECP> alter table tt modify p1 null;
Table altered.

Ok, it seems to work, but does it really? The answer is no, obviously.
A DESC of the table TT shows that the column is still NOT NULL. It
appears that Oracle quietly ignores errors.

Name Null? Type