We use ALTER TABLE ADD COLUMN command to add columns to an existing table. Using this command we can add a single column or multiple columns at once. We can even specify NOT NULL clause as well as DEFAULT clause.
You can add columns to an table using ALTER TABLE command only if you are the owner of the table. You can modify structure of other users table only if the table's owner has granted you the permission to modify structure of his tables. Otherwise Oracle will return insufficient privileges error.
EXAMPLES
How to add a single column to an existing table.
Let's say suppose we have a table by name "emp" with the following structure.
Now we want to add a column "city" to this table. To do that, we will issue the following command
alter table emp add city varchar2(20);
After issuing the above command the table structure will look like this
To fill the values in CITY column we need to use UPDATE command to set city values for different employees. Like
update emp set city='New York' where empno=7782
update emp set city='Dallas' where empno=7934
How to alter table add multiple columns to an existing table
If you want to add multiple columns to employee table you can give the following statement
For example, to add addr, pin, ph, fax to employees table we will give the following command
alter table emp add (addr varchar2(20),
pin varchar2(10),ph varchar2(20));
How about adding columns with NOT NULL constraint
As we have seen above, when we add a column, the column will be empty and we need to issue UPDATE statement to fill the values in that column. So the question is, Can we add a column with NOT NULL constraint? The answer is yes, we can add a column with NOT NULL constraint, but, there must 2 conditions which needs to be met
The table must either be empty or if the table is not empty then, you must also specify the DEFAULT value.
If you give the following command when table is not empty, you will get the following error
alter table emp add (city varchar2(100) not null);
However, if you also specify default value then it will succeed
alter table emp add (city varchar2(100) default "Paris' not null);
Alter table adding a PRIMARY KEY column
You can also add a column to an existing table with PRIMARY KEY constraint only if the table is empty and if there is no PRIMARY KEY already existing in the table.
To add a column with PRIMARY KEY constraint, give the following command
alter table emp add (EmpID varchar2(20) constraint emp_pk primary key);
If you try to add a column with PRIMARY KEY constraint and if the table is not empty then, it will through the following error
or if there is already a column with primary key constraint then, Oracle will through the following error
How to ALTER TABLE MODIFY columns in Oracle
How to ALTER TABLE DROP column in Oracle