Data Definition Language ( 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.
CREATE TABLE <Tablename> Example to create a table ‘Customer’ : Drop Command is used to drop table and other database objects. Key-points : 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. Rename Command is used to rename tables and other database objects. 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 to DROP EXISTING COLUMN and CONSTRAINT- ALTER Command to CHANGE DATA TYPE of column – Alter Command can be used to change datatype of a column. 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. 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. ALTER Command to ENABLE and DISABLE a Constraint – ALTER Command can be used for enabling and disabling constraints.
(<Col1Name> <datatype>(size), <Col2 Name> <datatype>(size), ……<Coln Name> <datatype>(size));
CREATE TABLE Customer (ID number(10), Name varchar2(10),City varchar2(8));
2. DROP :
DROP TABLE <Tablename>;
E.g. Drop a table ‘ customer’
DROP TABLE Customer ;
DROP TABLE <Tablename> PURGE;
E.g.
DROP TABLE Customer PURGE ;3. RENAME :
RENAME <OldTablename> TO <NewTablename>;
E.g.
RENAME Customer TO Cust;4. ALTER :
ALTER TABLE <Tablename>
ADD <ColName> <datatype>(size);
E.g.
ALTER TABLE Cust
ADD Mobile number(10);
ALTER TABLE <Tablename>
MODIFY <ColName> <datatype>(size) <new constraint name>;
E.g.
ALTER TABLE Cust
MODIFY ID number(10) NOT NULL;
ALTER TABLE <Tablename>
DROP COLUMN <ColName>;
E.g.
ALTER TABLE Cust
DROP COLUMN Mobile;
ALTER TABLE <Tablename>
DROP CONSTRAINT <ConstraintName>;
E.g.
ALTER TABLE Cust
DROP COLUMN Mobile;
ALTER TABLE <Tablename>
MODIFY <ColName> <newdatatype>(size);
E.g.
ALTER TABLE Cust
MODIFY ID Varchar2(1);
ALTER TABLE <Tablename>
MODIFY <ColName> datatype(<new size>);
E.g.
ALTER TABLE Cust
MODIFY Name Varchar2(20);
ALTER TABLE <Tablename> RENAME TO <New Tablename>;
E.g. Rename Customer table to Cust.
ALTER TABLE Customer RENAME TO Cust;
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 TABLE <Tablename>
DISABLE CONSTRAINT<ConstraintName>;
E.g.
ALTER TABLE <Tablename>
DISABLE CONSTRAINT sys-coo7323;
ALTER TABLE <Tablename>
ENABLE CONSTRAINT<ConstraintName>;
E.g.
ALTER TABLE <Tablename>
ENABLE CONSTRAINT sys-coo7323;
