#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

MySQL Constraints Explained: PRIMARY KEY, UNIQUE, CHECK & More

A constraint is a rule the database enforces on every insert and update, whatever program sends the data. Application code can have bugs, and a manual fix late at night can skip validation. A constraint cannot be skipped. MySQL 8.0 supports six kinds:

ConstraintRule it enforcesError when broken
NOT NULLThe column must have a value1048
DEFAULTFills a value when the insert leaves the column outnone (it is a fallback)
UNIQUENo two rows share the value (multiple NULLs are allowed)1062
PRIMARY KEYUNIQUE + NOT NULL; one per table; identifies the row1062
FOREIGN KEYThe value must exist in the parent table1452 / 1451
CHECKA condition must be true (enforced from 8.0.16)3819

One table, all the rules

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE employees (
  emp_id     INT AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(80) NOT NULL,
  full_name  VARCHAR(60) NOT NULL,
  dept_id    INT,
  salary     DECIMAL(10,2) NOT NULL,
  status     VARCHAR(10) NOT NULL DEFAULT 'active',
  CONSTRAINT uq_emp_email        UNIQUE (email),
  CONSTRAINT chk_salary_positive CHECK (salary > 0),
  CONSTRAINT chk_status          CHECK (status IN ('active','on_leave','exited')),
  CONSTRAINT fk_emp_dept         FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

INSERT INTO departments VALUES (10,'Finance'), (20,'Support');
INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('neha@example.com','Neha Rawat',10,52000);

Naming constraints (CONSTRAINT uq_emp_email ...) is optional, but the name appears in error messages and makes them easy to trace. Now try to break each rule.

Breaking the rules on purpose

UNIQUE: a second row with the same email

INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('neha@example.com','Neha R.',20,48000);
ERROR 1062 (23000): Duplicate entry 'neha@example.com' for key 'employees.uq_emp_email'

NOT NULL: a missing name

INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('arjun@example.com', NULL, 20, 41000);
ERROR 1048 (23000): Column 'full_name' cannot be null

CHECK: a negative salary, an unknown status

INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('arjun@example.com','Arjun Pillai',20,-5);

INSERT INTO employees (email, full_name, dept_id, salary, status)
VALUES ('arjun@example.com','Arjun Pillai',20,41000,'retired');
ERROR 3819 (HY000): Check constraint 'chk_salary_positive' is violated.
ERROR 3819 (HY000): Check constraint 'chk_status' is violated.

FOREIGN KEY: a department that does not exist

INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('arjun@example.com','Arjun Pillai',30,41000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`con`.`employees`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))

The rows that did get in

INSERT INTO employees (email, full_name, dept_id, salary)
VALUES ('arjun@example.com','Arjun Pillai',20,41000);
INSERT INTO employees (email, full_name, salary)
VALUES ('meera@example.com','Meera Das',39000);

SELECT emp_id, email, dept_id, salary, status FROM employees;
emp_idemaildept_idsalarystatus
1neha@example.com1052000.00active
4arjun@example.com2041000.00active
5meera@example.comNULL39000.00active

Three details are worth noticing:

The parent side of a foreign key

DELETE FROM departments WHERE dept_id = 20;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ...

The default action is to refuse. You can choose ON DELETE CASCADE, SET NULL or RESTRICT when you define the key. Each option is demonstrated in primary key vs foreign key in MySQL.

Listing and changing constraints

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'con' AND TABLE_NAME = 'employees'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;
CONSTRAINT_NAMECONSTRAINT_TYPE
chk_salary_positiveCHECK
chk_statusCHECK
fk_emp_deptFOREIGN KEY
PRIMARYPRIMARY KEY
uq_emp_emailUNIQUE

(con is the database name used here.) NOT NULL and DEFAULT are column properties, so they appear in DESCRIBE employees instead. To change a CHECK, drop it and add it again:

ALTER TABLE employees DROP CHECK chk_status;
ALTER TABLE employees ADD CONSTRAINT chk_status
  CHECK (status IN ('active','on_leave','exited','retired'));

Adding a constraint to a table that already has data fails if any existing row breaks it, so clean the data first.

Gotchas

For where constraints sit in the whole CREATE TABLE statement, see how to create a table in MySQL. To decide how tables should be split before you add keys, read normalization: 1NF, 2NF and 3NF.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →