Using your mind in every thing (alter table add column with constraint scenario )

In the name of Allah

If you have any thing want to do think , imagine and dream how to do this thing

improve your dreams

then start to do

Real example

Problem : adding column to database table with NOT NULL constrain

First Thinking (Thinking code is under)

Copy the data to temp table

Truncate the table

Insert the old data with new values

NO IT'S OBSELETE WAY

Second Way

Altering the table with the new column but without the constraint

Update the column with the desired value

Alter the table again with the constraint

MORE EFFICIENT BUT SURE THERE ARE BETTER WAYS

Third way

Alter the table with default value

Third way (another method)

Alter the table with DEFERRABLE parameter

which didn't enforce the constraint in the old data

first

I won't write this code try it your self

second

ALTER TABLE my_table
ADD (
CREATED DATE,
CREATOR VARCHAR2(8))
/

UPDATE my_table SET CREATOR='EXPRESS' , CREATED=SYSDATE;

ALTER TABLE RENTER_CAR
MODIFY (
CREATED DATE NOT NULL ,
CREATOR VARCHAR2(8) NOT NULL
)
/

third

ALTER TABLE my_table
ADD (
CREATED DATE default sysdate not null,
CREATOR VARCHAR2(8) default user not null
)

Third way (2nd)

ALTER TABLE my_table
ADD (
CREATED DATE not null DEFERRABLE ,
CREATOR VARCHAR2(8) not null DEFERRABLE
)
/