Alter Command in SQL

Tables created in a database need not be perfect. We may later find requirement for some new columns, or some columns as irrelevant, or need to modify the existing column or add / drop constraints on the table. These operations can be done by using ALTER statements.

Table of Contents

To add Column :

New columns can be added to table as below :

ALTER TABLE BOOKS ADD PUBLISHER_ID NUMBER;

We can even specify constraints on the new columns like we do while creating the tables.

ALTER TABLE BOOKS ADD PUB_ID NUMBER DEFAULT 0;

To Modify Column :

Existing columns can be modified by using ALTER statements. We can increase/decrease the size of the column, or we can completely change the datatype of the column. When we are decreasing the size of the column with values, then column values will be trimmed to new size, if it has more length. Similarly, when we are changing the datatype of a column with value, we need to make sure that values are compatible with new datatype. Otherwise it will not allow changing the datatype and we might have to delete the values in entire column and then change it. For example, numbers cannot be converted to DATE or vice versa.

ALTER TABLE BOOKS MODIFY PUBLISHER_ID VARCHAR2 (3);

ALTER TABLE BOOKS MODIFY PUBLISHER_ID VARCHAR2 (9);

To Drop Column :

If a column is irrelevant to the table, then we can delete them by using ALTER statements like below:
ALTER TABLE BOOKS DROP COLUMN CATEGORY_NAME;

To Add Constraints :

Using ALTER statement, we can even add or modify any constraints to the table.

ALTER TABLE BOOKS ADD CONSTRAINT pk_isbn PRIMARY KEY (ISBN); --primary key constraint
ALTER TABLE BOOK_LOAN--foreign key constraint
ADD CONSTRAINT fk_BL_BRWR FOREIGN KEY (BRWR_ID) REFERENCES BORROWER (BRWR_ID);

ALTER TABLE BOOKS ADD CONSTRAINT cnstr_bk_nm UNIQUE (BOOK_NAME); 
ALTER TABLE BOOK_RETURNS MODIFY FINE_AMOUNT   DEFAULT 0; --Modifying a constraint

To Drop Constraints :

Any named constraints can be dropped using ALTER statement.

ALTER TABLE BOOKS DROP PRIMARY KEY; -- dropping primary keys
ALTER TABLE BOOKS DROP CONSTRAINT chk_cnstr; -- dropping any named constraint
ALTER TABLE BOOKS DROP CONSTRAINT cnstr_bk_nm; -- dropping check constraint

Constraints that are created or dropped are verified in view USER_CONSTRAINTS like below :

SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'BOOKS';