Oracle Constraints error

Question

I have a table x, and added a new column abc of number data type. New column successfully loaded with null values into table x.

When I was trying to add the same column with not null constraint, its giving an error : "table must be empty to add mandatory (not null) column"

I expected an error because as there is no data in it, I can't use not null constraint. But, what was not expecting this error. Why must the table be empty to add that constraint ? Could some one explain ?

just imagine, what could database do with the existing nulls, when you mark something not null. So, the table has to be empty! Also,A ddl can never touch the existing data.. But for truncate, which actually just wipes out, the entire data. — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Jul 31, 2014 at 15:31

Hugh Jones · Accepted Answer · 2014-07-31 15:31:27Z

1

It is because the null constraint is immediately violated as soon as you create the column. You could perhaps supply a default value.

An empty table would not have this problem due to lack of rows.

answered Jul 31, 2014 at 15:31

Hugh Jones

2,7641 gold badge20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sampathsris · Accepted Answer · 2014-07-31 15:38:00Z

0

If you don't have data in the table, NOT NULL constraint is not violated. But if you have at least a single row, the constraint is violated because database have to create a column value for each row as NULL.

You can use a default value to overcome this issue:

ALTER TABLE tablename
ADD column_name NUMBER NOT NULL
DEFAULT '*';

answered Jul 31, 2014 at 15:38

sampathsris

22.4k12 gold badges74 silver badges102 bronze badges

Collectives™ on Stack Overflow

Oracle Constraints error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related