When you create a schema, it may be possible that you forget to add some columns or constraints in a table. SQL Alter Table Statement gives you the capability to change the structure or definition of an existing table.
What can be done with SQL Alter Table Statement?
You can do following with SQL Alter Table Statement:
- Add a new column
- Drop an existing column
- Modify data-type/ length of an existing column
- Define Primary Key for the table
- Define Foreign Key for column(s)
- Define Default value for a column
- Define Check Constraint for a column
- Define Unique Constraint for a column
- Drop Primary Key Constraint of a table
- Drop Foreign Key Constraint of a column
- Drop Check Constraint of a column
- Drop Unique Constraint of a column
Syntax of SQL Alter Table Statement
For Columns | For Constraints |
ALTER TABLE table-name ADD column-name datatype(length) [CONSTRAINT constraint-name ] PRIMARY KEY|FOREIGN KEY|UNIQUE|NOT NULL| DEFAULT FOR |CHECK constraint-definition; | ALTER TABLE table-name ADD CONSTRAINT constraint-name PRIMARY KEY|FOREIGN KEY|UNIQUE|NOT NULL| DEFAULT FOR |CHECK (Column-Name or definition) ; |
ALTER TABLE table-name MODIFY existing-column-name new-data-type and length; | |
ALTER TABLE table_name DROP COLUMN column_name; | ALTER TABLE table_name DROP CONSTRAINT constraint_name; |
Note: You can drop constraints using SQL Alter Table Statement only when the constraints have been named at the time of table creation.
In Livesql.oracle.com you cannot use the DESC command to see the structure of a table. You can use this command
select * from all_tab_columns where table_name=’table-name’ .
Remember to write the table-name in uppercase since the all_tab_columns system table stores the schema table names in uppercase
Add a new column
ALTER TABLE toybrands ADD MinOrder number(10)

Drop an existing column
ALTER TABLE toybrands DROP COLUMN minorder

Modify data-type/ length of an existing column
ALTER TABLE toybrands MODIFY brandName varchar2(80)

ALTER TABLE toybrands MODIFY minOrder varchar2(20)


For the remaining SQL Alter Table Statements this following table is added to the database, that contains the billing details for a purchased toy.

Define Primary Key for the table
ALTER TABLE Billing ADD CONSTRAINT pk_billno PRIMARY KEY (billno);
Define Foreign Key for column(s)
ALTER TABLE billing ADD CONSTRAINT fk_toyID FOREIGN KEY (toyid) REFERENCES toystore(toyid)
Define Default value for a column
ALTER TABLE billing MODIFY billdate date DEFAULT ’01-jan-20′;
Define Check value for a column
ALTER TABLE billing ADD CONSTRAINT chk_toyID CHECK (billamt>0)
Define Unique Constraint for a column
ALTER TABLE billing ADD CONSTRAINT unq_email UNIQUE (custemail)
Drop Primary Key Constraint of a table
ALTER TABLE billing DROP PRIMARY KEY
ALTER TABLE billing DROP CONSTRAINT pk_billno;
Drop Foreign Key Constraint of a column
ALTER TABLE billing DROP CONSTRAINT fk_toyID
Drop Check Constraint of a column
ALTER TABLE billing DROP CONSTRAINT chk_toyID
Drop Unique Constraint of a column
ALTER TABLE billing DROP CONSTRAINT unq_email;
In this post you have learned how to use SQL Alter Table statement to modify an existing data table in Oracle.
Be First to Comment