MySQL Constraint

Previous Page
Next Page

Constraints are used to restrict the way our data is put in the MySQL table. Constraints are used to ensure data integrity and accuracy. 

There are two types of Constraints that we can use:

  1. Table-level constraints: The constraints are applied to the entire table, not to a particular column.  
  2. Column-level constraints: These apply to specific columns. The column-level constraints that MySQL supports are:
  • NULL: a column with this constraint allows NULL values. By default, this constraint is applied to all columns.
  • Not NULL: a column with this constraint cannot have null values. If we try to insert a NULL value in such a column, MySQL throws a "<column-name> cannot be NULL" error.
  • Default: We can specify a default value for a column. In such a case, if a value is not specified, then the default value is put in.
  • PRIMARY_KEY: The Primary key constraints automatically make a column have unique values. The Primary key column is used to identify records in a table uniquely.
  • FOREIGN_KEY: The FOREIGN KEY constraints are used to connect two tables using the primary keys of both tables.
  • Unique: This constraint enforces the rule that the records cannot have duplicate values.
  • Auto_Increment: The Auto-increment constraint is used to create a series. The generated values are auto-inserted.

An example of creating a table with these constraints is as follows:

CREATE TABLE EMPLOYEES(
    EMP_ID INT AUTO_INCREMENT,
    FIRST_NAME VARCHAR(20) NOT NULL,
    LAST_NAME VARCHAR(20) UNIQUE,
    BIRTH_DATE DATE DEFAULT '1900-00-00',
    HIRE_DATE DATE DEFAULT (CURRENT_DATE()),
    PRIMARY KEY(EMP_ID)
);

CREATE TABLE EMPLOYEE_DEPT(
   RECORD_ID INT AUTO_INCREMENT PRIMARY KEY,
   EMP_ID INT,
   DEPT_ID INT,
   FROM_DATE DATE,
   TO_DATE DATE,
   FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEES(EMP_ID) On UPDATE cascade ON DELETE CASCADE,
   FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID) On UPDATE restrict ON DELETE CASCADE
);

Next, let's see a few examples of the constraints in action.

1. Auto-increment and DEFAULT constraints in MySQL

The Auto-increment generates a series and automatically inserts a value in the table. If no value is specified for a DEFAULT column, the default value we define is inserted.

Example:

insert into employees(FIRST_NAME,LAST_NAME)
values('Thorin','Oakenshield');

2. Not Null constraint violated in MySQL

If the column is defined as "Not NULL" and we try to insert a NULL value in it, MySQL throws an error.

Example:

insert into employees(FIRST_NAME,LAST_NAME)
values(NULL,'Oakenshield');

3. Null constraint in MySQL

If a column is defined as Null or if no constraint is mentioned in front of it, MySQL allows Null/Not Null values. 

Example:

insert into employees(FIRST_NAME,LAST_NAME)
values('Thrain',NULL);

4. Primary_key violation in MySQL

A column that is defined as a Primary Key is unique and Not Null. If either of these conditions are not met, MySQL throws an error.

Example:

insert into employees
values(2,'Smaug','The Destoyer',default,default);

5. Foreign Key violation in MySQL

If we try to insert a record for a foreign key that does not exist in a table, MySQL throws an error.

Example:

The foreign_key :: dept_id =10 does not exist.

insert into employee_dept
values(default,1,10,'1200-01-01','2800-10-15');

Previous Page
Next Page