Friday, 22 June 2018

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.

***********







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






No comments:

Post a Comment

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...