Live SQL

add nullable column with default value

View All Scripts Login to Run Script

Script Name add nullable column with default value
Description Test case in response to http://stackoverflow.com/questions/44005270/add-column-in-oracle-with-default-value-and-nullable-column-type?noredirect=1#comment75080427_44005270
Area SQL General / DDL
Contributor Boneist
Created Wednesday May 17, 2017

Statement 1

create table test (col1 number)

Table created.

Statement 2

alter table test modify col1 number default 99

Table altered.

Statement 3
Ensure we have a new column with a default value.
```
alter table test add col2 number default 99
```
```
Table altered.
```
Statement 4
Inserting a value for just one of the columns - what will happen to the second column for that row?
```
insert into test (col1)  
values (1)
```
```
1 row(s) inserted.
```
Statement 5
Insert values for both columns; null for the second column. What will the row show now?
```
insert into test (col1, col2)  
values (2, null)
```
```
1 row(s) inserted.
```
Statement 6
The first row has the default value for the second column, but the second row has the specified null value.
```
select * from test
```
```
COL1 COL2
1 99
2  - 

2 rows selected.
```
Statement 7
```
drop table test
```
```
Table dropped.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database