| Using column defaults for 'Not Null' columns [message #374703] |
Mon, 25 June 2001 15:35  |
Bob Slydell
Messages: 1
Registered: June 2001
|
Junior Member |
|
|
Our developers got lazy when they were coding for SQL Server. In their insert statements, they explicitly inserted Nulls (empty quotes '') into columns defined as Not Null. SQL Server is rather forgiving in this regard and instead of erroring, it inserts the default value for that column.
But now we're migrating to Oracle and our developers are finding that Oracle isn't as forgiving. When they try to insert a null into a 'not null' column, Oracle won't perform the insert. Is there any way to define a table in Oracle such that it will use the default value for a 'not null' column, even when a SQL statement explicitly tries to insert a null?
Thanks.
Bob Slydell
|
|
|
|
| Re: Using column defaults for 'Not Null' columns [message #374711 is a reply to message #374703] |
Tue, 26 June 2001 00:54  |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Your developers might have set the default value in sql server enterprised manager.
You can do the same in oracle also.
You can do in the following way:-
1) When you want to make a column to not null, you have to
enter some data in all the rows for that particular column.
Hence we have to update that particular column to some unique
value with single update statement.
Suppose UPDATE MYTEST SET SALARY = 10000 WHERE SALARY IS NULL;
2) Then modify the table structure with the statement
alter table mytest modify (salary number default 100 NOT NULL);
Now you may have to identify which column should be set the
default value. Hereafter any insert into this table,without
a value for the salary field, will take 100 by default.
Regards,
Giridhar Kodakalla
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
