Data Definition Language – DDL

DDLs are used to define the metadata of the database. i.e.; using this, we create schema, tables, constraints, indexes in the database.  DDLs are also used to modify Schema, tables index etc. Basically, using DDL statements, we create skeleton of the database. It helps to store the metadata information like number of schemas and tables, their names, columns in each table, indexes, constraints etc in the database.

Some of the DDL commands are

Table of Contents

Create

Create is used to create schema, tables, index, and constraints in the database. The basic syntax to create table is as follows.

CREATE TABLE tablename (Column1 DATATYPE, Column2 DATATYPE, … ColumnN DATATYPE);

CREATE TABLE STUDENT (STUDENT_ID CHAR (10), STUDENT_NAME CHAR (10));

This DDL statement creates STUDENT table with his ID and Name.

An index on the table is created as follows. These created indexes are not visible to the users, but they internally help to run the query quickly.

CREATE INDEX index_name on Table_name (column_name)
CREATE INDEX std_name ON STUDENT (STUDENT_FIRST_NAME, STUDENT_LAST_NAME);

Here std_name is the index, which is created on First name and last name in STUDENT table.

Usually constraints are created along with table creation. Constraints are defined on the columns of the table. They define the characteristic of the column. There are different types of Constraints present.

    1. NOT NULL – This constraint forces the column to have non-null value. We cannot enter/update any NULL value into such columns. It must have valid value all the time. For example, each student in STUDENT table should have class specified. No student can exist without class. Hence class column in the STUDENT table can be made NOT NULL.
CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER);
    1. UNIQUE – This constraint ensures, the column will have unique value for each row. The column value will not repeat for any other rows in the table.

Passport number of individual person is unique. Hence passport column in the PERSON table is made UNIQUE. It avoids duplicate entry of passport number to other persons.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL UNIQUE
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER);
OR
CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER
CONSTRAINT uc_StdID UNIQUE (STUDENT_ID));
    1. PRIMARY KEY – This constraint is another type of UNIQUE constraint. This constraint forces the column to have unique value and using which, we can uniquely determine each row.

As we have seen in STUDENT example, STUDENT_ID is the primary key in STUDENT tables.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL PRIMARY KEY,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER);
OR
CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER
CONSTRAINT pk_StdID PRIMARY KEY (STUDENT_ID));
    1. FOREIGN KEY – This constraint helps to map two or more tables in the database. It enforces parent-child relationship in the DB. Foreign key in the child table is the column which is a primary key in the parent table.

For example, each employee works for some department. Hence to map employee and department tables, we have to have DEPARMENT_ID of DEPARTMENT table in EMPLOYEE table too.  DEPARTMENT_ID is the primary key in DEPARTMENT table (Parent table) and is foreign key in EMPLOYEE table (Child table).

CREATE TABLE EMPLOYEE (EMPLOYEE_ID VARCHAR2 (10) PRIMARY KEY,
EMP_NAME VARCHAR2 (50),
DOB DATE,
……
DEPT_ID NUMBER
CONSTRAINT fk_DeptId FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT (DEPARTMENT_ID));
    1. CHECK – This constraint is used to check for specific conditions on the column. For example, if age has to be entered between 25 and 32, we can use CHECK Constraint. This will not allow to enter the age32.
CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER CHECK (AGE >= 25 and AGE<= 32));
    1. DEFAULT – This constraint specifies the default value to be entered when no value is entered to it. Suppose whenever we enter an entry in the STUDENT table, apart from Student details we also have to store the date when it is being entered. This entry would always be SYSDATE. Instead of entering it each time when we do an entry, if we set the default value of this column as SYSDATE, this column will be always inserted with SYSDATE. If we need to override this value with any other date, then we have to explicitly insert the new date.
CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER,
…..
CREATED_DATE DATE DEFAULT SYSDATE);

Alter

Suppose we have created a STUDENT table with his ID and Name. Later we realize that this table should have his address and Age too. What do we do at this stage? We will add the column to the existing table by the use of ALTER command.

This command is used to modify the structure of the Schema or table. It can even used to add, modify or delete the columns in the table. The syntax for alter statement is as follows.

To add a new column: ALTER TABLE table_name ADD column_name datatype;
To delete a column: ALTER TABLE table_name DROP COLUMN column_name;
To modify a column: ALTER TABLE table_name MODIFY column_name datatype;
To rename table: ALTER TABLE table_name RENAME TO new_table_name;
To rename the column: ALTER TABLE table_name RENAME COLUMN old_Column_name to new_Column_name;

Suppose we want to add Address column to the STUDENT table.

ALTER TABLE STUDENT ADD Address varchar2 (100);

Once we add new columns to the existing table, the column value for the existing data would be NULL. If we need value in them, either we have to set some default value or we need to explicitly update each column with proper value.

Suppose we want to Drop Age column from STUDENT table.

ALTER TABLE STUDENT DROP COLUMN AGE;

Once column is dropped, the entire information in the column is lost.

Suppose we want to modify the DOB column to have only year in it.

ALTER TABLE STUDENT MODIFY DOB NUMBER (4);

Note that, in order to change the one datatype to another datatype, that particular column should not have any value. If we are changing the length of the column, then we can do this with data in the column. If the length of the column is reduced, then the value in the column will be trimmed to adjust with new length.

Suppose name column is modified from Varchar2 (20) to Varchar2 (10) and one of the name was ‘Albert Einstein’. After column modification, the name would be automatically trimmed to ‘Albert Ein’

Alter command is even used to modify the indexes and constraints of the table.

ALTER INDEX index_name RENAME TO new_index_name;

DROP: – DROP statement is used to remove the table or index from the database. It can even be used to remove the database.  Once the DROP statement is executed, the object will not available for use.

 
DROP TABLE table_name;
DROP INDEX index_name;
DROP DATABASE database_name;
DROP TABLE STUDENT;

Truncate

Truncate statement is used to remove the content of the table, but keeps the structure of the table. This simply removes all the records from the table. No partial removal of data is possible here. It also removes all the spaces allocated for the data.

TRUNCATE TABLE table_name;
TRUNCATE TABLE STUDENT;

Comment

We have created some tables and columns in the database. After some days, when developer visits back these tables and columns, wonders why he has created it. He has forgotten, what was the purpose of creating those columns in the table? So what could be done in this case? We need some method to store the description of the tables and columns. Some columns would take only few values like 0 for vegetarian, 1 for eggetarian and 2 for non-vegetarian. But seeing 0, 1 or 2, we will not know what it is.  If we store those values somewhere, it would be helpful in future to enter the values. For this purpose, we use a command – COMMENT.

As the name says, it allows adding comments to tables, views and columns in the database. These comments are stored in the data dictionary of the database. This helps the developer to understand what the column is.

Comment of Table/view: COMMENT ON TABLE table_name IS ‘text’;

Comment of Column: COMMENT ON COLUMN table.column IS ‘text’

For Example:

COMMENT ON TABLE STUDENT IS ‘This table has all the details of Students’;
COMMENT ON COLUMN STUDENT.DOB IS 'Date of Birth of the Student'

If we need to drop the comment, then we just have to give the comment without any text.

COMMENT ON TABLE STUDENT IS ‘’;
COMMENT ON COLUMN STUDENT.DOB IS ‘’;
Translate »