MySQL-Constraints

CONSTRAINTS:

A Constraint is a condition or checks applicable on a field or set of fields.

Constraints are also known as integrity constraints because they applied to maintain data integrity.

Two types of constraints:

1. Column constraints: Apply only to individual columns.
2. Table constraints:    Apply to group of one or more columns. Syntax:

CREATE TABLE <table name>

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

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

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

 

The fields in parenthesis after the table constraints are the fields to which they apply.

 The column constraints apply to the columns whose definition they follow.

Example:

CREATE TABLE emp

( Eno integer NOT NULL,

Ename char(20) NOT NULL,

Job char(20) NOT NULL,

Salary decimal );

The above command creates emp table, in which column Eno, Ename, and Job can never have NULL values. Any attempt to put NULL values in these columns will be rejected.

 

Different Constraints are:-

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. DEFAULT
5. CHECK
6. FOREIGN KEY

 

NOT NULL Constraint:-

1. The NOT NULL constraint enforces a column to NOT accept NULL values.
2. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. Example:

CREATE TABLE emp

( Eno integer NOT NULL,

Ename char(20) NOT NULL,

Job char(20) NOT NULL,

Salary decimal );

 

The above table enforces the Eno column and the Ename column to not accept NULL values.

 

NOTE:

MySQL also supports some other constraint such as ENUM and SET constraint that are used to define columns that can contain only a given set of values.

 

UNIQUE Constraint: –

1. This constraint ensures that no two rows have the same value in the specified columns.
2. When applied to the columns ensure that there cannot exist more than one NULL value in the column. Example:

 

CREATE TABLE emp

( Eno integer NOT NULL UNIQUE,

Ename char(20) NOT NULL,

Job char(20) NOT NULL,

Salary decimal );

 

Primary Key Constraint: –

1. This constraint declares a column as the primary key of the table. Only one column or one combination can be applied in this constraint.
2. The primary key cannot allow NULL values, thus these constraints must be applied to columns declared as NOT

Example:

CREATE TABLE emp

( Eno integer NOT NULL PRIMARY KEY ,

Ename char(20) NOT NULL,

Job char(20) NOT NULL,

Salary decimal );

 

Here, multiple constraints NOT NULL and UNIQUE have been applied on one column by putting space in between. The comma (,) comes in the end of column definition.

 

Default Constraint:

1. A default value can be specified for a column using the DEFAULT clause.
2. When a user does not enter a value for the column, automatically the defined default value is inserted in the field. A column can have only one default value.

Example:

CREATE TABLE emp

( Eno integer NOT NULL PRIMARY KEY ,

Ename char(20) NOT NULL,

Job char(20) DEFAULT = ‘CLERK’,

Salary decimal );

 

Here, if no value is provided for job, the default value of ’CLERK’, will be entered.

 

Check Constraint:

•            This constraint limits values that can be inserted into a column of the table. Check constraint will not work because The CHECK clause is parsed but ignored by all storage engines.

OR

Sometimes we may require that values in some of the columns of our table are to be within a certain range or they must satisfy certain conditions.

 

Example:

CREATE TABLE emp

( Eno integer NOT NULL PRIMARY KEY ,

Ename char(20) NOT NULL,

Job char(20) NOT NULL,

Salary decimal CHECK > 2000 );

 

This statement ensures that the value inserted for salary must be greater than 2000When a check constraint involves more than one column from the same table, it is specified after all the column have been defined.

 

Example:

 

Create table Emp

( ENo integer(4) Primary Key,

EName char(20) Not Null,

Salary integer(6,2) check (salary > 0),

DeptNo integer(3)

 

);

 

NOTE:

CHECK constraint involves more than one column from the same table, it is specified after all the columns have been defined, which we will see in letter slides.

 

6. Foreign Key Constraint:

Tables reference one another through common fields and to ensure the validity of references, referential integrity is enforced. Referential integrity is ensured through FOREIGN KEY constraint.

OR

The foreign key designates a column or combination of columns as a foreign key and establishes its relationship with a primary key in a different tables.

 

Syntax: columnname dataype (size) REFERENCES tablename [ (columnname) ]

 

Persons (P_Id, Name, Address, City)             Here, P_ID is a primary key in a Persons

Orders (O_Id, Ono, P_Id)                                 table and foreign key in a Orders table.

 

E.g.

CREATE TABLE Persons

( P_Id integer(10) NOT NULL PRIMARY KEY ,

Name char(10),

Address char(20),

City char(20) );

 

CREATE TABLE OrderS

( O_Id integer(10), Ono integer(10),

P_Id integer(10) REFERENCES Persons(P_Id),

);                                                                                  //Foreign key Parent table’s name

 

NOTE:

The foreign key constraint can also be applied through FOREIGN FEY table constraint

 

Create table Fee

(RollNo integer(2) Foreign key (Rollno) references Student (Rollno),

Name char(20) Not null,

Amount integer(4),

Fee_Date date

);