Data Definition Language ( DDL Commands )

Data definition language or DDL commands

What are DDL Commands ??

Data Definition Language ( DDL Commands ) as the name suggests , are used to define and modify the database objects definitions. They are auto – saved or Auto – commit commands i.e. after being executed ,the changes made by them are automatically saved in the database permanently.

 

Types of  DDL Commands :

1. CREATE  :

Create Command is used to create table and other database objects.

Key-points:

  • While creating a table , If size attribute is not defined with ‘Number’ datatype , then by default it takes the size as 37. For ‘Char’ datatype it takes default as 1 but for ‘Varchar2’ datatype, size is mandatory.
  • The ‘ Table Name ‘ attribute specified in CREATE statement can have minimum one character and maximum 30 characters. Similar is the case with ‘Column Name’ attribute.
  • One Table may have maximum of 1000 columns defined.
  • A Table may contain multiple columns , but none of the two columns can have the same name. Similarly a schema may contain multiple Tables but none of the two tables can have the same name.
  • Irrespective of the case we give while defining the table name , internally it is stored in upper case.

Syntax for creating a table

CREATE TABLE <Tablename>
(<Col1Name>   <datatype>(size), <Col2 Name>  <datatype>(size), ……<Coln Name>  <datatype>(size));

Example to create a table ‘Customer’ :
CREATE  TABLE  Customer (ID  number(10), Name varchar2(10),City  varchar2(8));

2. DROP  : 

Drop Command is used to drop table and other database objects.

Key-points :

  • DROP command drops the table completely i.e. it deletes the data along with the table structure and other associated structures.
  • It removes the table from the data dictionary and releases the space immediately.
  • Once the table is dropped , it cannot be rolled back.
Syntax:To Drop a Table
DROP TABLE <Tablename>;
E.g. Drop a table ‘ customer’
DROP  TABLE  Customer ;

New Feature of Oracle 10g ( further releases also) for DROP Command :

DROP with Roll -Back facility using FLASHBACK –

Until prior releases of Oracle 10g , Table once dropped , could not be rolled back. But Oracle 10g introduced a new feature for Drop Command , where it can be rolled back. It drops the table only temporarily and does not release the space immediately.It renames and stores the table and its associated structure in the recycle bin from where it can be restored. FLASHBACK option is used to roll back.

Permanent Drop using PURGE –

In order to drop the table permanently, and release the space immediately , PURGE Option is used. Tables dropped with PURGE option cannot be rolled back.

Syntax:To drop a table permanently
DROP TABLE <Tablename> PURGE;
E.g.
DROP  TABLE  Customer PURGE ;

3. RENAME  :

Rename Command is used to rename tables and other database objects.

Syntax:
RENAME  <OldTablename>  TO  <NewTablename>;
E.g.
RENAME  Customer  TO  Cust;

4. ALTER :

Alter Command is used to alter the structure of table and other database objects. ALTER Command can be used to perform the following functions.

ALTER Command to ADD NEW COLUMN and NEW CONSTRAINT –

  • Alter Command can be used to add new column to the table.
  • It can also be used to add new constraint after creating the table.
Syntax to add new column:
ALTER TABLE  <Tablename>
ADD  <ColName> <datatype>(size);
E.g.
ALTER TABLE  Cust
ADD  Mobile  number(10);
Syntax to add new constraint:
ALTER TABLE  <Tablename>
MODIFY  <ColName> <datatype>(size)  <new constraint name>;
E.g.
ALTER TABLE  Cust
MODIFY ID number(10)  NOT NULL;

ALTER Command to DROP EXISTING COLUMN and CONSTRAINT-

  • Alter Command can be used to remove existing column from a table.
  • It can also be used to remove existing constraint on the table.
Syntax to drop existing column:
ALTER TABLE  <Tablename>
DROP COLUMN  <ColName>;
E.g.
ALTER TABLE  Cust
DROP COLUMN  Mobile;
Syntax to drop existing column:
ALTER TABLE  <Tablename>
DROP CONSTRAINT <ConstraintName>;
E.g.
ALTER TABLE  Cust
DROP COLUMN  Mobile;

ALTER Command to CHANGE DATA TYPE of column –

Alter Command can be used to change datatype of a column.

Syntax to change data type of column:
ALTER TABLE  <Tablename>
MODIFY <ColName>  <newdatatype>(size);
E.g.
ALTER TABLE  Cust
MODIFY ID Varchar2(1);

ALTER Command to CHANGE SIZE of column –

Alter Command can be used to change column size of the table.

We cannot decrease the size of the column beyond the max size of the value in the column. For e.g. suppose Column ‘Location’ who current size is varchar2(10)  holds value of maximum size  9 ( consider karnataka), In such case the size cannot be reduced to any value less than 7. Reducing the size to varchar2(6) will throw an error.

Syntax to change size of column:
ALTER TABLE  <Tablename>
MODIFY <ColName> datatype(<new size>);
E.g.
ALTER TABLE  Cust
MODIFY Name Varchar2(20);

ALTER Command to rename database objects ( except VIEWS) , tables  and constraints.-

Alter Command can be used to rename columns and tables.

Note : ALTER VIEW command does not support RENAME option. To Rename Views independent Rename command has to be used.

Syntax to disable a constraint:
ALTER TABLE  <Tablename> RENAME TO <New Tablename>;
E.g. Rename Customer table to Cust.
ALTER TABLE  Customer RENAME TO Cust;
Syntax to disable a constraint:
ALTER TABLE  <Tablename>
RENAME  CONSTRAINT <OldConstraintName>  TO  <NewConstraintName>;
E.g. Rename unique constraint defined on table Demo2
ALTER TABLE  Demo2
RENAME CONSTRAINT  D2_Sno_unq  TO  demo2_Sn_unq;

ALTER Command to ENABLE and DISABLE a Constraint –

ALTER Command can be used for enabling and disabling constraints.

Syntax to disable a constraint:
ALTER TABLE  <Tablename>
DISABLE  CONSTRAINT<ConstraintName>;
E.g.
ALTER TABLE  <Tablename>
DISABLE  CONSTRAINT sys-coo7323;
Syntax to enable a constraint:
ALTER TABLE  <Tablename>
ENABLE  CONSTRAINT<ConstraintName>;
E.g.
ALTER TABLE  <Tablename>
ENABLE  CONSTRAINT sys-coo7323;
Print Friendly, PDF & Email
SQL Statements .
Indexes
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...