SQL Constraint

Constraints are the rules enforced on data columns on table. These 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 column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

Common types of constraints include the following:

• NOT NULL Constraint: Ensures that the column does not accept NULL values.
• DEFAULT Constraint: Provides a default value for a column when none is specified.
• UNIQUE Constraint: Ensures that all values in a column are different.
• CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
• Primary Key Constraint: Used to uniquely identify a row in the table.
• Foreign Key Constraint: Used to ensure referential integrity of the data.

SQL NOT NULL CONSTRAINT:

create table employee(eno int NOT NULL,ename varchar(25) NOT NULL,eaddress varchar(25));

+----------+---------------+------+------+---------+-------+

 | Field     | Type             | Null  | Key | Default | Extra |

+----------+---------------+------+------+---------+-------+

| eno         | int(11)         | NO   |          | NULL  |          |

| ename    | varchar(25)  | NO   |         | NULL  |          |

| eaddress | varchar(25) | YES  |         | NULL  |          |

+----------+---------------+------+------+---------+-------+

SQL UNIQUE CONSTRAINT:

create table employee1(eno int NOT NULL,ename varchar(24) NOT NULL,eaddress varchar(24),UNIQUE(eno));

+----------+---------------+------+-----+---------+-------+

| Field      | Type             | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| eno         | int(11)         | NO   | PRI | NULL    |       |

| ename    | varchar(24)  | NO   |        | NULL    |       |

| eaddress | varchar(24) | YES  |        | NULL    |       |

+----------+-------------+--------+-----+---------+-------+      

SQL UNIQUE CONSTRAINT ON ALTER TABLE:

alter table employee1 add UNIQUE(ename);

+----------+-------------+- -  - --+ -----+------- --+-------+

| Field      | Type           | Null    | Key | Default | Extra |

+----------+-------------+----   --+-- --- +- ---- ----+-------+

| eno         | int(11)         | NO    | PRI | NULL    |         |

| ename    | varchar(24) | NO    | UNI | NULL    |         |

| eaddress | varchar(24) | YES  |         | NULL    |         |

+----------+-- ------ -----+----- -+- ----+--- ------+-------+

SQL PRIMARY KEY CONSTRAINT:

create table employee2(eno int NOT NULL,ename varchar(25) NOT NULL,eaddress varchar(25),PRIMARY KEY(eno));

+----------+---------- ---+------+----- +---------+-------+

| Field      | Type           | Null | Key  | Default | Extra |

+----------+----------- --+------+----- +---------+-------+

| eno         | int(11)         | NO  | PRI | NULL   |         |

| ename    | varchar(25) | NO   |        | NULL   |         |

| eaddress | varchar(25) | YES |        | NULL   |         |

+----------+-------------+------+-----+---------+-------+

SQL FOREIGN KEY CONSTRAINT:

create table employee2(eno int NOT NULL,ename varchar(25) NOT NULL,eaddress varchar(25),PRIMARY KEY(eno),FOREIGN KEY(ename)REFERENCES employee2(ename));

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| eno      | int(11)     | NO   | PRI | NULL    |       |

| ename    | varchar(25) | NO   | MUL | NULL    |       |

| eaddress | varchar(25) | YES  |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

SQL CHECK CONSTRAINT:

create table employee5(eno int NOT NULL,ename VARCHAR(20) not null,eaddress varchar(20),CHECK(eno));

+----------+---- ------ ---+------+-----+---------+-------+

| Field      | Type            | Null | Key | Default | Extra |

+----------+----- ---- ----+------+-----+---------+-------+

| eno         | int(11)         | NO   |       | NULL    |         |

| ENAME | varchar(20) | NO   |       | NULL    |        |

| eaddress | varchar(20)  | YES |       | NULL   |         |

+----------+----- --- -- ---+------+-----+---------+-------+

SQL DEFAULT CONSTRAINT:

mysql> create table employee6(eno int NOT NULL,ename varchar(25) NOT NULL,eaddress varchar(25) DEFAULT 'chennai');

+----------+----------- --+------+-----+---------+-------+

| Field      | Type           | Null | Key | Default | Extra |

+----------+------------ -+------+-----+---------+-------+

| eno         | int(11)        | NO   |       | NULL    |       |

| ename    | varchar(25) | NO   |      | NULL    |       |

| eaddress | varchar(25) | YES |       | chennai |       |

+----------+-------------+------+-----+---------+-------+