DDL in SQL is the short form of Data Definition Language. It is one of the categories of SQL language.
Commands under DDL are:
CREATE
ALTER
DROP
TRUNCATE
RENAME
These commands are under DDL because they make changes to the data stored.
CREATE (Table Creation)
The syntax for creating a table is,
CREATE TABLE table_name(column_name_1 datatype,
column_name_2 datatype,
column_name_3 datatype)
Here, we have added the datatype in addition to the column name, other than the datatype we can add more attributes(constraints) like size, e.g. varchar(30), here the number 30 is size.
E.g. CREATE TABLE department( dept_no NUMBER, dept_name VARCHAR(20), city VARCHAR(20));
CREATE TABLE Employee(empl_no NUMBER(10), name VARCHAR(25) designation VARCHAR(20), salary NUMBER(20), dept_no NUMBER);
Integrity Constraints
These are used for maintaining the validity of the data. i.e., to make sure the data is entered in the right way.
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
NOT NULL
They are used when we need a mandatory column that should not accept NULL VALUE.
NOT NULL means we cannot insert a new record, or update a record without adding a value to this field. i.e., this should not be empty.
UNIQUE
This constraint is applied to a particular column where no identical values can be used.
Here, more than one column can be defined as unique.
PRIMARY KEY
The primary key is an attribute that identifies a record/tuple/row set uniquely
i.e. unique row = primary key
PRIMARY KEY = NOT NULL + UNIQUE
One table contains only one primary key. But, one table can have more than one unique key.
FOREIGN KEY
Integrity maintained by reference is called foreign key constraints or referential integrity constraints.
The relationship between the tables is defined using this constraint.
CHECK & DEFAULT CONSTRAINTS
CHECK - to limit or define the value range that can be given in a column.
DEFAULT - to insert a default value into a column. This value will be assigned to the records if no value is specified.
E.g. 1. CREATE TABLE dept (dept_no NUMBER PRIMARY KEY, dept_name VARCHAR(20) UNIQUE, city VARCHAR(20);
E.g.2. CREATE TABLE employee (Emp_no NUMBER PRIMARY KEY ,Name VARCHAR(25) NOT NULL , Designation VARCHAR(20) DEFAULT TRAINEE, Salary NUMBER (20) CHECK(Salary>=1000),Dept_no NUMBER REFERENCES dept(dept_no));
ALTER Objects
ALTER command is used to drop, modify, or add any constraints.
E.g. ALTER TABLE employee DROP COLUMN_name;
ALTER TABLE employee ADD(name varchar(25);
ALTER TABLE employee MODIFY(name varchar(26));
ALTER TABLE employee ADD CONSTRAINT ADDRESS CHECK(SALARY>1000);
DROP Objects
To drop objects such as indexes/ column_name and tables. i.e. they can be easily deleted/removed.
Syntax - DROP OBJECT object_name.
If we want the table to remain but its content should be deleted, then the TRUNCATE command can be used.
Syntax - TRUNCATE TABLE table_name.
Comments