ALTER TABLE statement in SQL

ALTER TABLE statement in SQL 

ALTER TABLE statement

ALTER TABLE is a Data Definition Language (DDL) statement that alters the structure definition of an existing database table. This statement can be used to alter the structure along with the following commands;

Command	Purpose	Conditions must hold
ADD	To add new attributes (columns) with the existing table	You can either add a NOT NULL attribute with the empty relation (table) or add only NULL attributes if the table is have some records in it.
MODIFY	To modify the data type and size of existing attributes	While you modify, the new data type and size should not be less than the old type and size if the table has data. you can modify table as per your wish only if it is empty (without records).
DROP COLUMN	To remove existing attribute	Attribute can be removed only if it is not being referred by another attribute/another table
DROP CONSTRAINT	To remove existing integrity constraints (CHECK, FOREIGN KEY, UNIQUE, PRIMARY KEY)	We need to check whether the attribute is referred by other attributes/other table.
ADD CONSTRAINT	To add new constraints on existing attributes	New constraints can be added if the table is empty. Else, adding constraints is a complex task.

Syntax:

ALTER TABLE table_name

[ADD (column_name1 datatype(size), column_name2 datatype(size), …]

[MODIFY (column_name new_datatype(size)]

[DROP COLUMN existing_column_name]

[DROP CONSTRAINT existing_constraint_name]

[ADD CONSTRAINT constraint_name constraint constraint_definition];

Examples:

Let us consider the Furnitures table that was created using the following DDL statement for our example;

CREATE TABLE Furnitures (

Furniture_ID CHAR(5),

Furniture_Name VARCHAR(25) NOT NULL,

Furniture_Type VARCHAR(20) NOT NULL,

Manufacturer VARCHAR(25) NOT NULL,

Quantity NUMBER(5),

Design_No NUMBER(5),

CONSTRAINT pk PRIMARY KEY (Furniture_ID),

CONSTRAINT ch1 CHECK (Quantity > 10),

CONSTRAINT ch2 CHECK (Furniture_Type in (‘Chair’, ‘Table’, ‘Cot’))

);

1) ADD

The following statement will add a new attribute PRICE with furnitures table;

ALTER TABLE furnitures ADD (Price NUMBER(7,2));

Before ALTER	After ALTER
Attributes Furniture_ID Furniture_Name Furniture_Type Manufacturer Quantity Design_No	Furniture_ID Furniture_Name Furniture_Type Manufacturer Quantity Design_No Price

2) MODIFY

The following statement will modify the manufacturer attribute with new size;

ALTER TABLE furnitures MODIFY (Manufacturer VARCHAR(35) NOT NULL);

Before ALTER	After ALTER
Manufacturer VARCHAR(25) NOT NULL	Manufacturer VARCHAR(35) NOT NULL

3) DROP COLUMN

The following statement will remove Design_No column from furnitures table;

ALTER TABLE furnitures DROP COLUMN Design_No;

Before ALTER	After ALTER
Attributes Furniture_ID Furniture_Name Furniture_Type Manufacturer Quantity Design_No Price	Furniture_ID Furniture_Name Furniture_Type Manufacturer Quantity Price

4) DROP CONSTRAINT

The following statement will remove the CHECK constraint with the name ch1; [Note: it will not remove the column on which check constraint applied]

ALTER TABLE furnitures DROP CONSTRAINT ch1;

Before ALTER	After ALTER
Quantity must be a value greater than 10	Quantity can be any number of size not more than 5 Constraint ch1 removed

5) ADD CONSTRAINT

The following statement will add a new constraint on newly added attribute Price;

ALTER TABLE furnitures ADD CONSTRAINT ch3 CHECK (Price BETWEEN 1500 AND 20000);

Before ALTER	After ALTER
No constraint on Price	Price must be in the range 1500 and 20000.

***********

Go to CREATE TABLE page

Go to SQL home page

alter table statement in sql oracle, 

how add, modify, drop attributes in a database table, 

how to add a new integrity constraint with the existing table, 

how to remove an integrity constraint from a database table definition  

when can we modify a database table using ALTER TABLE statment

the conditions to be checked before modifying a database table definition

important considerations before redefining a table structure in SQL