PL/SQL - Can you add not null column to a table already containing data ?

GeekInterview.com > Interview Questions > Oracle > PL/SQL

Go To First | Previous Question | Next Question

PL/SQL | Question 227 of 241

Can you add not null column to a table already containing data ?

Total Answers and Comments: 10

Last Update: July 07, 2009 Asked by: be_viral

Sponsored Links

Best Rated Answer

—

Submitted by: svaparna08

We can add a not null column but it should be defaulted to some value.

Above answer was rated as good by the following members:
Pradeep_R, suresbala

July 15, 2008 20:34:46

narasingu

Member Since: July 2008 Contribution: 1

RE: Can you add not null column to a table already containing data ?

No. You canot add a NULL Column. See the error below:
ERROR at line1:
ORA-01758: table must be empty to add mandatory(NOT NULL) column

Is this answer useful? Yes | No

1 0

Overall Rating: +1

August 01, 2008 04:34:01

rethishr

Member Since: August 2008 Contribution: 1

RE: Can you add not null column to a table already containing data ?

Yes But we have to Specify a Default value for the column

Is this answer useful? Yes | No

August 07, 2008 01:24:35

gtomar

Member Since: July 2008 Contribution: 9

RE: Can you add not null column to a table already containing data ?

Hi

Yes you can alter table after creation for adding not null constraint.

syntax is :-

ALTER TABLE tablename

MODIFY (column name) (datatype) NOT NULL;

for eg :-

ALTER TABLE EMP

MODIFY ename VARCHAR2(10) NOT NULL

Thanks
Gunjan

Is this answer useful? Yes | No

October 21, 2008 07:45:35

svaparna08

Member Since: January 2008 Contribution: 1

RE: Can you add not null column to a table already containing data ?

We can add a not null column but it should be defaulted to some value.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

December 16, 2008 00:08:04

kantri

Member Since: October 2008 Contribution: 4

RE: Can you add not null column to a table already containing data ?

Yes we can add a not null constraint to a column but every row should have a data for that column.

Is this answer useful? Yes | No

April 07, 2009 05:14:38

Nattamai

Member Since: April 2009 Contribution: 1

RE: Can you add not null column to a table already containing data ?

1. Create the new column
2. Insert dummy value
3. Enable the NOT NULL constraint.

Is this answer useful? Yes | No

June 06, 2009 16:14:30

MAjay

Member Since: June 2009 Contribution: 1

RE: Can you add not null column to a table already containing data ?

Yes

ALTER TABLE <Table Name>

MODIFY <Column Name> NOT NULL;

Ex:

ALTER TABLE scfg MODIFY scfg1 VARCHAR2(10) NOT NULL;

If you have null values in the column then you have to update all with some
default value using update script otherwise it will give you error: Null Value
Found

Is this answer useful? Yes | No

June 08, 2009 00:23:51

dj_dj_dj

Member Since: May 2009 Contribution: 12

RE: Can you add not null column to a table already containing data ?

Yes you can add NOT NULL column to a table that already contains data. There would not be any error such that. But only the new records inserted would be affected.

Is this answer useful? Yes | No

June 08, 2009 00:30:27

neelapu

Member Since: June 2009 Contribution: 3

RE: Can you add not null column to a table already containing data ?

NOT NULL constraint cannot be added at table level
To add the constraint you need to modify the column
Ex:-
ALTER TABLE emp MODIFY ename NOT NULL;

Is this answer useful? Yes | No

July 07, 2009 16:02:12

#10

javedans

Member Since: January 2009 Contribution: 8

RE: Can you add not null column to a table already containing data ?

1. By default you cannot add not null constraints which already have null value in the column.
2. You need replace some dummy values to null value to meet requirement and then add constraint to that column.

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank