DDL Commands

DDL Commands

CREATE TABLE Command

You can create a table by using the CREATE TABLE command. When a table is created, its columns are named, data types and sizes are to be specified. Each table must have at least one column. The syntax of CREATE TABLE command is :

CREATE TABLE <table-name>

(<column name><data type>[<size>]

(<column name><data type>[<size>]……

);

Now let us use the above syntax to store some information about the students of a class in a database, for this you first need to create table. To create a student table, let us take some information related to students like admission number which we can use it in short form as (admno), name of student (name), gender, age etc. of the student. Let us create a table having the field names Admno, Name, Gender, Age and Place.

The SQL command will be as follows:

CREATE TABLE Student

(Admno integer,

Name char(20),

Gender char(1),

Age integer,

Place char(10),

);

The above one is a simple table structure without any restrictions. You can also set constraints to limit the type of data that can go into the fields of the table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level.

Column constraint: Column constraint apply only to individual column.

Table constraint : Table constraint apply to a group of one or more columns.

The syntax for a table created with constraint is given as below:

CREATE TABLE <table-name>

(<column name><data type>[<size>]<column constraint>, (<column name><data type>[<size>]<column constraint>……

<table constraint>(<column name>,[<column name>….])…..

);

Following is an example for student table with “NOT NULL” column constraint. This constraint enforces a field to always contain a value.

CREATE TABLE Student

(

Admno integer NOT NULL PRIMARY KEY, → Primary Key constraint

Name char(20)NOT NULL,

Gender char(1),

Age integer,

Place char(10),

);

The above command creates a table “student” in which the field Admno of integer type is defined NOT NULL, Name of char type is defined as NOT NULL which means these two fields must have values. The fields Gender, Age and Place do not have any constraints.

Type of Constraints

Constraints ensure database integrity, therefore known as database integrity constraints. The different types of constraints are :

(i)Unique Constraint

This constraint ensures that no two rows have the same value in the specified columns. For example UNIQUE constraint applied on Admno of student table ensures that no two students have the same admission number and the constraint can be used as:

CREATE TABLE Student

(

Admno integer NOT NULL UNIQUE, → Unique constraint

Name char (20) NOT NULL,

Gender char (1),

Age integer,

Place char (10),

);

The UNIQUE constraint can be applied only to fields that have also been declared as NOT NULL.

When two constraints are applied on a single field, it is known as multiple constraints. In the above Multiple constraints NOT NULL and UNIQUE are applied on a single field Admno, the constraints are separated by a space and at the end of the field definition a comma(,) is added. By adding these two constraints the field Admno must take some value ie. will not be NULL and should not be duplicated.

ii) Primary Key Constraint

This constraint declares a field as a Primary key which helps to uniquely identify a record. It is similar to unique constraint except that only one field of a table can be set as primary key. The primary key does not allow NULL values and therefore a field declared as primary key must have the NOT NULL constraint.

Example showing Primary Key Constraint in the student table:

CREATE TABLE Student

(

Admno integer NOT NULL PRIMARY KEY, → Primary Key constraint

Name char(20)NOT NULL,

Gender char(1),

Age integer,

Place char(10),

);

In the above example the Admno field has been set as primary key and therefore will help us to uniquely identify a record, it is also set NOT NULL, therefore this field value cannot be empty.

(iii) DEFAULT Constraint

The DEFAULT constraint is used to assign a default value for the field. When no value is given for the specified field having DEFAULT constraint, automatically the default value will be assigned to the field.

Example showing DEFAULT Constraint in the student table:

CREATE TABLE Student

(

Admno integer      NOT NULL PRIMARY KEY,

Name char(20)NOT NULL,

Gender char(1),

Age integer DEFAULT = “17”, → Default Constraint

Place char(10),

);

In the above example the “Age” field is assigned a default value of 17, therefore when no value is entered in age by the user, it automatically assigns 17 to Age.

(iv) Check Constraint

This constraint helps to set a limit value placed for a field. When we define a check constraint on a single column, it allows only the restricted values on that field. Example showing check constraint in the student table:

CREATE TABLE Student

(

Admno integer      NOT NULL PRIMARY KEY

Name char(20)NOT NULL,

Gender char(1),

Age integer (CHECK<=19), → Check Constraint

Place char(10),

);

In the above example the check constraint is set to Age field where the value of Age must be less than or equal to 19.

(v) TABLE CONSTRAINT

When the constraint is applied to a group of fields of the table, it is known as Table constraint. The table constraint is normally given at the end of the table definition. Let us take a new table namely Student1 with the following fields Admno, Firstname, Lastname, Gender, Age, Place:

CREATE TABLE Student 1

(

Admno integerNOT NULL,

Firstname char(20),

Lastname char(20),

Gender char(1),

Age integer,

Place char(10),

PRIMARY KEY (Firstname, Lastname) → Table constraint

);

In the above example, the two fields, Firstname and Lastname are defined as Primary key which is a Table constraint.