SQL ALTER TABLE Statement

After the table( s) are created and begin to be used, requirements are likely to occur which requires modifications in the structure of the table, such as adding new columns to the table, modifying and existing column’s definition etc. So these modifications can be performed using the ALTER table DDL statement. Tills statement changes the structure of the table and not its contents. There are many types of modifications that can be made to the structure of the tables. Using ALTER TABLE statement, you can make modifications such as

• Add or drop one or more columns to i from existing table.

• Increase or decrease the width of the existing column.

• Change an existing column from mandatory to optional or vice versa.

• To enable or disable integrity constraints.

• Add/ Modify / Delete integrity constraints associated to / from the table.

• Specify a default value for existing column.

NOTE: TO ALTER a table, the table must be contained in your schema or you must have either the ALTER object privilege for the table or ALTER any table system privilege.

ADDING NEW COLUMNS CONSTRAINTS IN EXISTING TABLE

We have already discussed that we can alter the structure of the table using ALTER TABLE statement, once the table is being created. The ALTER TABLE statement helps the designer to make necessary changes to the existing table instead of creating the table from the scratch.

Now we shall discuss how the ALTER TABLE statement helps to add new columns as well as constraints into the existing table.

The syntax is ALTER TABLE <tablename> 
ADD (column_specification / constrain_specification, .... 
column_specification / constrain_specification); 

Where tablename corresponds to the name of the table, column_specification corresponds to the valid specification for a column (columnname and datatype) and constraint specification corresponds to valid constraint specification.

• To add address column into the INSTRUCTOR table with column width 30 and data type varchar2 is shown below.

SQL> ALTER TABLE instructor ADD (address VARCHAR2(30)); 
Table altered. 

Instead of adding columns one by one we can add multiple columns in the table. To add address and salary columns into the INSTRUCTOR table, the statement is

SQL> ALTER TABLE instructor ADD (address VARCHAR2 (30), Salary NUMBER (10, 2)); 
Table altered. 

Similarly you can add constraints to the existing table. If you forget to add primary key constraint to the table at the time of creation, you can add it later using ALTER TABLE statement. Similarly you can add other constraints such as foreign key, check, unique etc.

• Now let us suppose that no primary key constraint exists for INSTRUCTOR table because if it exists then we cannot define a new one unless we drop the existing one we add a primary key constraint with inst_id column of the INSTRUCTOR table, using ALTER TABLE statement given below.

SQL> ALTER TABLE instructor ADD (CONSTRAINT PK_INSTRUCTOR PRIMARY KEY( INST_ID)); 
Table altered. 

• Now let us assume that no foreign key exists in the INSTRUCTOR table which references the (dept_id) primary key of DEPARTMENT table. So we add a foreign key constraint on the dept_id column of the INSTRUCTOR table which refers to the dept_id primary key column of the DEPARTMENT table, using the ALTER TABLE command given below.

SQL> ALTER TABLE instructor ADD (CONSTRAINT FK_INSTRUCTOR FOREIGN KEY (dept_id) REFERENCES department (dept_id));
Table altered.

The following points should be kept in mind when adding new columns / constraints into the existing tables.

• You can add one or more than one column at the same time.

• There is no need of parentheses; if you adding only a single column or constraint.

• Before adding a primary key makes sure that no duplicate values exist for a column (s) on which primary key is applied.

• Before adding a foreign key, make sure that primary key constraint must exist in the referring table.

• You may add a column at any time if NOT NULL is not specified. However, you can add a new column with NOT NULL only if the table is empty.

• If a table contains records and you add a new column (s) to this table, then the initial value of each row for the new column is NULL.

MODIFYING COLUMNS! CONSTRAINTS IN EXISTING TABLE

The ALTER TABLE statement also offers the ability to modify columns / constraints in the existing table without impacting any other data in the table. Using ALTER TABLE statement, we can increase or decrease the column widths, changing a column from mandatory to optional (NOT NULL to NULL) and vice versa.

The syntax is 
ALTER TABLE <tablename> 
MODIFY ( column_specification / constrain_specllcation,..... 
column_ specification / constrain_specllcation); 

To increase the width of name column in the INSTRUCTOR table from 15 to 20, use the following ALTER TABLE statement.

SQL> Alter table instructor MODIFY (name varchar2 (20)); 
Table Altered 

Oracle doesn’t allow you to decrease the column’s width even if all the column values are of valid length. So to reduce the width of the column, the values must be set to NULL. Decreasing the NAME column width from 15 to 10 is also not possible even if all the values in name column are less than equal to 10 characters unless all values in name column contain null.

You can modify a column from NULL to NOT NULL constraint if the table contains no records in that column. However, if the column contains values for each row of the table you can modify it from NULL to NOT NULL.

SQL> Alter table instructor MODIFY (name varchar2 (20) NOT NULL); 
Table Altered 

You can change the data type of the columns if column contains NULL values for every row of the table.

SQL> Alter table instructor MODIFY (name char (20)); 
Table Altered 

DROPPING COLUMNS/CONSTRAINTS

You can not only modify columns that exist in your tables but you can also drop them entirely. You can also remove the constraints from the table using the ALTER TABLE statement. When a constraint is dropped, any associated index with that constraint is also dropped.

The syntax for dropping a column is

ALTER TABLE <tablename> DROPCOLUMN <Columnname>; 

This syntax is valid if you want to drop one column at a time.

To drop the PHONE column from the INSTRUCTOR table, the ALTER TABLE statement is

SQL > Alter table instructor drop column phone; 

If the user wishes to drop multiple columns using the ALTER TABLE statement then to follow the following syntax.

ALTER TABLE <tablename> DROP (<columnname1>,< columnname2>) ; 

To drop the post and the name column from the INSTRUCTOR table, the ALTER TABLE statement is

ALTER table instructor DROP (post, name); 
Table Altered. 

To drop the primary key inst_id from the instructor table. Use the following ALTER TABLE statement.

SQL> ALTER table instructor DROP PRIMARY KEY; 
Table altered. 

If you know the name of the constraint, then you can drop all types of constraints like primary key, foreign key, check constraints etc. The syntax for executing above using ALTER TABLE statement is

ALTER TABLE <tablename>DROP CONSTRAINT <constraintname>; 

Suppose we want to drop the foreign key column DEPT_ID with constraint name FK_INSTRUCTOR then use the following statement.

SQL> ALTER table instructor DROP CONSTRAINT FK_INSTRUCTOR; 
Table altered. 

The following points should be kept in mind when dropping the column / constraints:

• You cannot drop all the columns in a table.

• You cannot drop a columns from tables whose owner is SYS.

• You cannot drop a parent, key column (primary key) unless you drop the foreign key that references it. To do this, CASCADE keyword is used.

SQL> alter table department DROP PRIMARY KEY CASCADE; 
Table altered. 

You can enable or disable the key constraints in situations like when loading large amount of data into a table, performing batch operations that make massive changes to a table, migrating the organization’s legacy data.

To disable constraint,
 
 SQL > Alter TABLE instructor Disable Constraint PK_INSTRUCTOR;
 
Table altered 

To enable constraint,

SQL > Alter TABLE instructor Enable Constraint PK_INSTRUCTOR;

Table altered

Instead of dropping a column, you can make the column unused and drop it later on, It thus makes the response time faster than if your executed the DROP clause. After a column has been marked as unused, you have no access to that column. The unused columns will not be retrieved with SELECT * query. The SET UNUSED information is stored in USER_UNUSED _COL_TABS dictionary view.

SQL> alter table instructor SET UNUSED COLUMN phone;