6/23/12

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

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_nameFor 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 employeeADD 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,