SQL Alter Table Statement

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)

SQl Alter Table add column

Drop an existing column

ALTER TABLE toybrands DROP COLUMN minorder

DROP COLUMN

Modify data-type/ length of an existing column

ALTER TABLE toybrands MODIFY brandName varchar2(80)

Modify Column

ALTER TABLE toybrands MODIFY  minOrder varchar2(20)

Before SQL Alter Statement
After SQL Alter Statement

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

Leave a Reply

Your email address will not be published.