Getting wrong error message when running a command

[kbcompany]

Hi

I have just started learning oracle.

i have created a table with name imp_master and columns item_code with constraint not null, item_name with constraint n1 not null(n1 is user defined constraint name), item_rate and item_desc.

the copmmand is as follows...

CODE:

create table item_master
( item_code varchar2(25) not null,
item_name varchar2(25) n1 not null,
item_rate number(7,2),
item_desc varchar2(50) );

table has been created with the above mentioned columns. i have checked it with the command " desc item_master;"

After this has been done i have altered the column "item_desc" with the command " alter table item_master modify item_desc not null; "
the column has been altered.
To check this i have entered the command
Insert into table item_master (item_desc) values (null);
The problem is here
instead of showing the message that the column item_desc does not take null values... it is throwing error message
ERROR MESSAGE :

ORA_01400 cannot insert null into
( "SCOTT"."ITEM_M ASTER"."ITEM_CO DE")

even though i am trying to enter nulls into "item_desc" column ... it is referring to "item_code" column.

After this i have modified the item_desc to accept the null values. And then i ran command to enter the values into the column. Then also it is throwing the error message as

ERROR MESSAGE:

ORA_01400 cannot insert null into
( "SCOTT"."ITEM_M ASTER"."ITEM_CO DE")

I am programming in oracle 9. OS is windows 200 advanced server.

Help appreciated.

[rpnew]

Hi,
You are doing following....

 Insert into table item_master (item_desc) values (null);
 

So here what is happening is you are inserting value(null in this case) for item_desc but you are not specifying any value for other columns so NULL will be inserted for them. And thats why you are getting this error cause you've specified NOT NULL constraints for first two columns so NULL is not allowed for them.
If you want to check your item_desc column for NULL try the following query

  Insert into table item_master (item_code,item_name,item_desc) values ('somevalue','somevalue',null);
 

For column item_rate you haven't specified any constraints so error will not be thrown.

Regards,
RP

[amitpatel66]

Well ,that is how oracle performs.
If you see your table structure, the first column ITEM_CODE is described as NOT NULL. So any insert statement not entering value for this column will throw this error.

If you try the below insert statement:

insert into ITEM_MASTER(item_code) values('1');
 

It will throw you an error saying "CANNOT INSERT NULL INTO item_name" and so on.
Oracle will take in order the columns from the table and throw an error accordingly!!

[kbcompany]

Thanks a lot guys....