MySQL Constraints
MySQL Constraints
In MySQL, constraints are rules applied to columns or tables to enforce data integrity and ensure the accuracy and reliability of data.
Constraints can be defined during table creation or added later using ALTER TABLE.
🔹 Types of MySQL Constraints
| Constraint | Description |
|---|---|
| PRIMARY KEY | Uniquely identifies each row in a table. A table can have only one primary key, but it can consist of multiple columns (composite key). |
| FOREIGN KEY | Enforces a link between columns in two tables to maintain referential integrity. |
| UNIQUE | Ensures that all values in a column are distinct. |
| NOT NULL | Ensures that a column cannot have NULL values. |
| DEFAULT | Sets a default value for a column if no value is specified. |
| CHECK | Ensures that values meet a specific condition. (Available in MySQL 8.0+) |
🔹 Examples
1️⃣ PRIMARY KEY
-
iduniquely identifies each student.
2️⃣ FOREIGN KEY
-
Ensures that
Students.dept_idmust exist inDepartments.dept_id.
3️⃣ UNIQUE
-
Prevents duplicate emails in the
Userstable.
4️⃣ NOT NULL
-
namecolumn cannot be NULL.
5️⃣ DEFAULT
-
If
marksis not specified, it defaults to0.
6️⃣ CHECK
-
Ensures
marksare between 0 and 100.
🔹 Key Points
-
Constraints enforce data integrity and validity.
-
Can be applied at the column level or table level.
-
Primary key + foreign key establish relationships between tables.
-
UNIQUE, NOT NULL, DEFAULT, CHECK help control valid input.
-
Constraints can be added or removed using
ALTER TABLE.
