CODEWITHSUNDEEP
sqloracle-database
benny
benny

Reputation: 17

Oracle Constraints error

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 ?

Upvotes: 0

Views: 52

Answers (2)

sampathsris
sampathsris

Reputation: 22290

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 '*';

Upvotes: 0

Hugh Jones
Hugh Jones

Reputation: 2694

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.

Upvotes: 1

Related Questions