Computer Chapter - - Structured Query Language 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( ) NOT NULL, Gender char( ), Age integer, Place char( ), ); 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 : Primary Key Constraint Default Constraint Check Constraint Unique Constraint Constraint . . . 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: 12th Computer Chapter - - CREATE TABLE Student ( Admno integer NOT NULL UNIQUE, → Unique constraint Name char ( ) NOT NULL, Gender char ( ), Age integer, Place char ( ), ); 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. . . .
Primary Key Constraint This constraint declares a field as a Primary key which helps to