Introduction to DDL Statements in SQL Server

Introduction

Most database users need not worry about creating a database; they use interactive SQL to access a database of corporate information or access a database that has been created by someone else. In a typical corporate database, for example, the database administrator may give you permission to retrieve and perhaps update the stored data. However, the administrator will not allow you to create new databases or to modify the structure of the existing tables.

Unfortunately for you, you will also be expected to know how to create databases and define their structure!

The SELECT, INSERT, DELETE, UPDATE, COMMIT and ROLLBACK statements are all concerned with manipulating the data in a database. These DML statements can modify the data stored in the database, but they cannot change its structure. None of these statements create or delete tables or columns for example. Changes to the structure of the database are handled by a different set of SQL statements, usually called the SQL Data Definition Language, or DDL.

Using DDL statements it is possible to:

• Define and create a new table

• Remove a table that is no longer needed

• Change the definition of an existing table

• Define a virtual table (view) of data

• Establish security controls for a database

• Build an index to make table access faster

• Control the physical storage of data by the DBMS

 The Data Definition Language is based on three SQL verbs:

• CREATE

  It defines and creates a database object

• DROP

  It removes an existing database object

• ALTER

  It  changes the definition of a database object

  
  

  Let us see the details of each statement.

CREATE TABLE Statement

The CREATE TABLE statement defines a new table in the database and prepares it to accept data. The various clauses of the statement specify the elements of the table definition. 

Syntax

Create table Table_name (column_ name (Data_type));

Note: Do not attempt to "learn from" these diagrams. This is simply provided as a sample for you to see the syntax.

The columns of the newly created table are defined in the body of the CREATE TABLE statement. The column definitions appear in a comma-seperated list enclosed in parentheses. The order of the column definitions determines the left-to-right order of the columns in the table. 

In the preceding syntax:

• The column _name, which is used to refer to the column in SQL statements. Every column in the table must have a unique name, but the names may duplicate those columns in other tables.

• The data_ type of the column, identifying the kind of data that the column stores. Some data types, such as VARCHAR and DECIMAL, require additional information, such as the length or number of decimal places in the data. This additional information is enclosed in parentheses following the keyword that specifies the data type.

• Whether the column contains required data. The NOT NULL clause prevents NULL values from appearing in the column; otherwise, NULL values are allowed.

• An optional default value for the column. The DBMS uses this value when an INSERT statement for the table does not specify a value for the column.

Example

       CREATE TABLE EMPLOYEE(ID INTEGER NOT NULL UNIQUE,SURNAME VARCHAR(30) NOT NULL,FIRSTNAME VARCHAR(30) NOT NULL,EMAIL VARCHAR(80),
        COUNTRY VARCHAR(30) DEFAULT 'India',PHONE VARCHAR(25))   

As seen in the first CREATE TABLE example above, it is possible to define "default" values for columns. If a column has a default value, it is specified within the column definition. Even if a column is defined as NOT NULL but has a default set, its omission in an INSERT statement would not cause an issue, since it would simply default to the chosen value.

In addition to defining the columns of a table, the CREATE TABLE statement identifies the table's primary key and the table's relationships to other tables in the database. The PRIMARY KEY and FOREIGN KEY clauses handle these functions. The PRIMARY KEY clause specifies the column or columns that form the primary key for the table. The FOREIGN KEY clause specifies a foreign key in the table and the relationship that it creates to another (parent) table in the database. The FOREIGN KEY clause specifies:

• The column or columns that form the primary key, all of which are columns of the table being created.

• The table that is referenced by the foreign key.

• An optional name for the relationship.

• How the DBMS should treat a NULL value in one or more columns of the foreign key.

• An optional delete rule for the relationship

• An optional update rule for the relationship

DROP TABLE

Sometimes it is necessary to dispose of old tables, which are no longer in use. This is done through the use of the DROP TABLE statement. The table name in the statement identifies the table to be dropped. Normally you will be dropping one of your own tables and will use an unqualified table name, but it is possible to drop a table owned by another user, assuming that you have the required permission. 

The syntax for the DROP table statement can be seen below:

Drop table Tbale_name  

Example

Drop table  Employee

ALTER TABLE

After a table has been in use for some time, users often discover that they want to store additional information about the entities represented in the table. Changes to table structures can be handled using the ALTER TABLE statement. 

Using the ALTER TABLE statement we can:

• Add a column to a table

• Drop a column from a table

• Change the default value for a column

• Add or drop a primary key for a table

• Add or drop a foreign key for a table

• Add or drop a constraint for a table

There are a number of issues which may occur when performing ALTER TABLE statements. First, when adding a new column to an existing table, the DBMS assumes a NULL value for all existing rows in the table. If the column is declared to be NOT NULL with a default value then the DBMS assumes the default value. It is not possible to simply declare the new column NOT NULL, because the DBMS would be immediately violating this constraint!

Dropping a column can pose similar problems as those encountered in deletes and updates. For example, if you drop a column that is a primary key in some relationship, the foreign key columns that refer to the dropped column become invalid. 

The SQL2 standard deals with these issues in the same way that it handled the potential data integrity problems posed by DELETE and UPDATE statements - with a drop rule that operates in a similar way to the delete and update rules. You can specify one of two drop rules; RESTRICT (causing an error if dependencies exist) and CASCADE (any other dependency objects are also dropped).

Summary

I hope this article is useful for all readers. If you have any suggestion then please contact me.