MySQL Constraints

Constraints are rules for the data in a column. MySQL refuses any data that breaks the rules, so your data stays correct.

Constraint	Rule
PRIMARY KEY	Unique and not empty. Identifies each row
NOT NULL	The column cannot be empty
UNIQUE	No two rows can have the same value
DEFAULT	Value used when none is given
AUTO_INCREMENT	MySQL gives the next number by itself
FOREIGN KEY	The value must exist in another table
CHECK	The value must pass a condition (MySQL 8.0.16 and above)

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT CHECK (age >= 18),
  status VARCHAR(10) DEFAULT 'active'
);

---

What happens when a rule is broken

INSERT INTO users (name, email, age) VALUES ('Robin', 'robin@example.com', 25);
INSERT INTO users (name, email, age) VALUES ('Maria', 'robin@example.com', 30);

INSERT INTO users (name, age) VALUES ('Neha', 15);

The status of the first row was not given, so MySQL used the default value active.

---

FOREIGN KEY

A foreign key connects two tables. Here every student must belong to a course that exists in the courses table.

ALTER TABLE students
  ADD FOREIGN KEY (course_id) REFERENCES courses(id);

Now inserting a student with course_id = 99, which is not in courses, is rejected. See MySQL JOINs.

---

Summary

• Constraints protect the data from wrong values.
• Use PRIMARY KEY in every table, and NOT NULL, UNIQUE and FOREIGN KEY where they fit.
• Next: MySQL INSERT.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.