How we can Add one column to the employee table and that column should be not null constraint...
I have already tried with my answers like this way...
ALTER TABLE EMP
MODIFY ename VARCHAR2(10) NOT NULL;...
or
Alter table emp
add col_extra number(7) check(col_extra is not null);(It's not worked)..
or
ALTER TABLE <YourTable>
ADD <NewColumn> <NewColumnType> NOT NULL DEFAULT <DefaultValue>....
Provide me a right one answer taking emp table with an example.
When you add a column to a table its value will be null. So to add a not null constraint first you need to provide a value!
The easiest way to do this is with a default:
alter table scott.emp add new_col int default 1 not null;
But this means you need to pick a suitable default. So you need to speak to your users/business analysts to find one. In many cases existing rows will need different values. So you'll need to update the column after adding it. You could:
- Add with the default and not null then update
Or
- Add the column, update it, then set to not null
For large tables it may be better to do the update as a create table as select:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6407993912330 There isn't one "right" answer here.