top of page
Writer's pictureanisa mumtaz

DDL IN SQL: A SIMPLE GUIDE

Updated: Sep 19





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.






Recent Posts

See All

Comments


bottom of page