SQL Data Definition Language (DDL) Tutorial

After writing a post on Data Manipulation Language (DML), here is another tutorial in SQL series on DDL i.e. Data Definition Language.

DDL (Data Definition Language) statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately. Some of the DDL commands are CREATE, ALTER, DROP, USE. 

* Another DDL statement is used to define the referential integrity, which we will see later in the tutorial.

CREATE statements

A CREATE statement is used to create table, index, stored query or database.

CREATE DATABASE (DB) statement

The CREATE DATABASE statement is used to create a Database.

SYNTAX : 

CREATE DATABASE database_name

CREATE TABLE statement

Perhaps the most common CREATE command is the CREATE TABLE command. The typical usage is:

CREATE TABLE [table_name] ( [column_definitions] ) [table parameters]

Column Definitions: A comma-separated list consisting of any of the following

* Column definition: [column name] [data type] {NULL | NOT NULL} {column options}

* Primary key definition: PRIMARY KEY ( [comma separated column list] )

* Constraints: {CONSTRAINT} [constraint definition]

For example, the command to create a table named employees with a few sample columns would be:

CREATE TABLE employees (

    id            INTEGER      PRIMARY KEY,
    first_name    VARCHAR(50)  NULL,
    last_name     VARCHAR(75)  NOT NULL,
    dateofbirth   DATE         NULL
);

DROP statement

The DROP command of the Data Definition Language, allows us to remove entire database, table, view or an index objects from our DBMS. 

SYNTAX :
DROP object_type object_name

For example, if we want to permanently remove the employee table that we created, we'd use the following command: 

** DROP TABLE employee 

---

NOTE : The DROP statement is different from the DELETE and TRUNCATE statement. As in DELETE statement it might delete some (or all) data from the table while leaving the table in the DB, and TRUNCATE statement just deletes all the records (rows) from the table permanently thus keeping the table in the DB whereas the DROP statement will remove the table permanently from the DB.

---

ALTER statement

The ALTER statement helps to modify an existing DB object. 

Once you've created a table within a database, you may wish to modify the definition of it. The ALTER command allows you to make changes to the structure of a table without deleting and recreating it. 

SYNTAX :

ALTER object_type object_name paramerters

Take a look at the following command: 

ALTER TABLE employee
ADD salary money null 

USE statement

The USE command allows us to specify the database we wish to work with within your DBMS. 

For example, if we're currently working in the department database and want to issue some commands that will affect the employees database, we would preface them with the following SQL command: 

USE employees 

It's important to always be conscious of the database you are working in before issuing SQL commands that manipulate data. 

REFERENTIAL INTEGRITY 

Referential integrity is a database concept that ensures that relationships between tables remain consistent. When a table has a foreign key to another table, then there has to be another key called the primary key in another table which helps in making the link and thus keeping the table consistent.

As we all by now know that PRIMARY KEY and FOREIGN KEY helps us to maintain referential integrity, so here is a small description on the same.

PRIMARY KEY

A primary key is one which uniquely identifies a row of a table. this key does not allow null values and also does not allow duplicate values.

FOREIGN KEY

A foreign key is one which will refer to a primary key of another table.

UNIQUE KEY

Its a single and main key. A unique is one which uniquely identifies a row of atable, but there is a difference like it will not allow duplicate values and it willany number of allow null values(In oracle). 

It allows only a single null value(In sql server 2000)

Both will function in a similar way but a slight difference will be there. So, 

decalaring it as a primary key is the best one.