Home » SQL & PL/SQL » SQL & PL/SQL » How to modify null column to not null (oracle 10g,windows server 2003 64 bit)
How to modify null column to not null [message #538702] Mon, 09 January 2012 01:57 Go to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
Hello when i follow this steps mention on this website " http://www.roseindia.net/sql/sql-alter-column-not-null.shtml " to modify column from null to not null i got this error and on this website its show successful

my steps are

first i create a table


SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
  2  Stu_Class  varchar(10));

Table created.


Then insert some rows into Stu_Table

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');

1 row created.


SQL> select * from Stu_Table;

ST STU_NAME   STU_CLASS
-- ---------- ----------
1  Komal
2  Ajay
3  Rakesh
4  Bhanu
5  Santosh


Table Structure is like this

SQL> Describe Stu_Table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 STU_ID                                             VARCHAR2(2)
 STU_NAME                                           VARCHAR2(10)
 STU_CLASS                                          VARCHAR2(10)



now when i try to modify this Stu_id column to not null its give me error.

SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
                                       *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


and when i try to add new column with not null its also gives me error

SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

Re: How to modify null column to not null [message #538704 is a reply to message #538702] Mon, 09 January 2012 02:06 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In order to alter a column to NOT NULL, you don't sepcify its type nor precision - just NOT NULL:
alter table stu_table modify stu_id not null;


You can't add a NOT NULL column into a table that already contains records (because they would automatically violate NOT NULL constraint). First add a column, insert some values in there (that would be the UPDATE statement), and - finally - alter a table to set the column NOT NULL (as above).
Re: How to modify null column to not null [message #538706 is a reply to message #538702] Mon, 09 January 2012 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
now when i try to modify this Stu_id column to not null its give me error.

The error does not come from the NOT NULL but from the "int(3)" which is not a valid datatype.

Quote:
and when i try to add new column with not null its also gives me error


You must give a default value if the table is not empty:
SQL> select count(*) from t;
  COUNT(*)
----------
         4

1 row selected.

SQL> alter table t add (newcol varchar2(20) default 'X' not null);

Table altered.

Regards
Michel
Re: How to modify null column to not null [message #538715 is a reply to message #538706] Mon, 09 January 2012 03:26 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks littlefoot.its helpfull me.
Re: How to modify null column to not null [message #538716 is a reply to message #538715] Mon, 09 January 2012 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And mine not? /forum/fa/1637/0/

Regards
Michel

[Updated on: Mon, 09 January 2012 03:50]

Report message to a moderator

Re: How to modify null column to not null [message #538717 is a reply to message #538704] Mon, 09 January 2012 03:35 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Littlefoot wrote on Mon, 09 January 2012 08:06
In order to alter a column to NOT NULL, you don't sepcify its type nor precision - just NOT NULL:
alter table stu_table modify stu_id not null;


You can't add a NOT NULL column into a table that already contains records (because they would automatically violate NOT NULL constraint). First add a column, insert some values in there (that would be the UPDATE statement), and - finally - alter a table to set the column NOT NULL (as above).



You can also specify a default column value as an alternate means, obviously it should then be removed once column added since default is going to prevent not null triggering Smile

Different means to the same end.
Re: How to modify null column to not null [message #538759 is a reply to message #538717] Mon, 09 January 2012 05:37 Go to previous message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
michel your also helpfull but littlefoot direcly give me this syntax and its so easy to catch so..hahh.in my small mind
Previous Topic: update job_id of employees table of HR schema
Next Topic: Write quey to achieve this result...
Goto Forum:
  


Current Time: Fri Apr 25 02:26:34 CDT 2025