SQL Constraints

Table Constraints

Table constraints defines the rules that limits the set of values for one or more columns of the base table.

Unique columns constraint

A Unique columns constraint for the table defines a rule that the values for the specified columns should not be duplicated in the entire table.

Syntax:
UNIQUE "(" UniqueColumns ")"

Eg:
CREATE TABLE MYTABLE ( COL1 INT, COL2 INT, COL3 INT, COL4 INT, UNIQUE (COL1, COL4) );

Usage:
UniqueColumns - List of "," separated column names which have to unique for the table.

Primary key constraint

A primary key for a table is the set of columns that must contain unique and non-null values, which can be used as a unique identifier to refer each row of that table. A table can have only one primary key, which is a set of one or more columns.

Syntax:
PRIMARY KEY "(" UniqueColumns ")"

Eg:
CREATE TABLE MYTABLE ( ID INT, NAME CHAR(10), PRIMARY KEY(ID) );

Usage:
UniqueColumns - List of "," separated column names which have to unique for the table.

Referential constraint

A Referential(Foreign key) constraint defines a rule that the referencing columns of a table(foreign table) can have only those values that are contained in the referenced columns of the referenced table.

Syntax:
FOREIGN KEY "(" ReferencingColumns ")"
REFERENCES TableName [ "(" ReferencedColumns ")" ]
[ MATCH ( FULL | PARTIAL | SIMPLE ) ]
[ ( ON UPDATE | ON DELETE )
( RESTRICT | CASCADE | SET NULL | SET DEFAULT | NO ACTION ) ]

Eg:
CREATE TABLE MYTABLE ( ID INT, PROJID INT, DESIGNATION VARCHAR(20), FOREIGN KEY (PROJID) REFERENCES OTHERTABLE(PID) ON DELETE RESTRICT );

Usage:

  • ReferencingColumns - List of "," separated column names of the foreign table, which forms foreign key.
  • ReferencedColumns - List of "," separated column names of the referenced table, which forms referenced key.
  • ON UPDATE or ON DELETE clause are used to specify the referential action to be performed on the foreign key table, when the referenced key is updated or deleted.
    • RESTRICT - Restricts the Update/Delete on the referenced key, if there are any foreign keys referencing to it.
    • CASCADE - The same action as that on the referenced key, is also performed on the foreign key values.
    • SET NULL - When the referenced key values are Updated/Deleted, the referencing foreign key values are set to null.
    • SET DEFAULT - When the referenced key values are Updated/Deleted, the referencing foreign key values are set to their default values.
    • NO ACTION - This is the default value taken, when no referential action is specified and it is same as that of the RESTRICT action.

Check constraint

Check constraint on a table defines the rule that every tuple(row) added to the table should satisfy the given check condition.

Syntax:
CHECK "(" Conditions ")"

Eg:
CREATE TABLE MYTABLE ( COL1 INT, COL2 INT, COL3 INT, CHECK (COL1 + COL2 > COL3) );

Usage:
Refer SearchCondition

Column Constriants

Column constraints defines the rules that limits the set of values for that particular column of the base table.

Null constraint

A Not null constraint defines a rule on that column value that it cannot be null.

Syntax:
NOT NULL

Eg:
CREATE TABLE MYTABLE ( ID VARCHAR(5) NOT NULL, NAME VARCHAR(20) );

Unique constraint

A Unique constriant on a column defines a rule that the value for this column has to be unique throughtout the base table it belongs to.

Syntax:
UNIQUE

Eg:
CREATE TABLE MYTABLE ( ID INT UNIQUE, NAME VARCHAR(10) );

Default value constraint

Default value constraint on a column defines a rule that whenever a value is not specified for this column, default value is inserted.

Syntax:
DEFAULT DefaultValue

Eg:
CREATE TABLE MYTABLE1 ( ID INT, NAME VARCHAR(20) DEFAULT '' ); CREATE TABLE MYTABLE2 ( ID INT, LOGIN_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Usage:
DefaultValue : - Default value can be a Literal, a System varaible or NULL
Refer Variables and Litervals

Primary key column constraint

Primary key constraint on a column defines a rule that the column is not null and unique throughtout the table and can be used to uniquely identify the rows.

Syntax:
PRIMARY KEY

Eg:
CREATE TABLE MYTABLE ( ID INT PRIMARY KEY, NAME VARCHAR(20) );

Check constraint

Check constraint on a column defines the rule that all the values added for this column should satisfy the given check condition on the column.

Syntax:
CHECK "(" Conditions ")"

Eg:
CREATE TABLE MYTABLE ( ID INT CHECK (ID > 10), NAME VARCHAR(20) );

Usage:
Refer SearchCondition

Column foreign key constraint

A foreign key constraint on a column defines a rule that this column forms the foreign key and references the specified column of the referenced table, which implies that this column can have only those values that are contained for the referenced column of the referenced table.

Syntax:
REFERENCES TableName [ "(" ReferencedColumns ")" ]
[ MATCH ( FULL | PARTIAL | SIMPLE ) ]
[ ( ON UPDATE | ON DELETE ) ( RESTRICT | CASCADE | SET NULL | SET DEFAULT | NO ACTION ) ]

Eg:
CREATE TABLE MYTABLE ( ID INT, PROJID INT REFERENCES OTHERTABLE(PID) ON UPDATE SET NULL, DESIGNATION VARCHAR(20) );

Usage:

  • ReferencedColumns - List of "," separated column names of the referenced table, which forms referenced key.
  • ON UPDATE or ON DELETE clause are used to specify the referential action to be performed on the foreign key table, when the referenced key is updated or deleted.
    • RESTRICT - Restricts the Update/Delete on the referenced key, if there are any foreign keys referencing to it.
    • CASCADE - The same action as that on the referenced key, is also performed on the foreign key values.
    • SET NULL - When the referenced key values are Updated/Deleted, the referencing foreign key values are set to null.
    • SET DEFAULT - When the referenced key values are Updated/Deleted, the referencing foreign key values are set to their default values.
    • NO ACTION - This is the default value taken, when no referential action is specified and it is same as that of the RESTRICT action.