MySQL Constraints

MySQL Tutorial

MySQL Constraints

In MySQL, constraints are rules applied to columns or tables to enforce data integrity and ensure the accuracy and reliability of data.

 Types of MySQL Constraints

ConstraintDescription
PRIMARY KEYUniquely identifies each row in a table. A table can have only one primary key, but it can consist of multiple columns (composite key).
FOREIGN KEYEnforces a link between columns in two tables to maintain referential integrity.
UNIQUEEnsures that all values in a column are distinct.
NOT NULLEnsures that a column cannot have NULL values.
DEFAULTSets a default value for a column if no value is specified.
CHECKEnsures that values meet a specific condition. (Available in MySQL 8.0+)

 Examples

 PRIMARY KEY

  • id uniquely identifies each student.

 FOREIGN KEY


 

  • Ensures that Students.dept_id must exist in Departments.dept_id.

 UNIQUE

  • Prevents duplicate emails in the Users table.

 NOT NULL

  • name column cannot be NULL.

 DEFAULT

  • If marks is not specified, it defaults to 0.

CHECK

  • Ensures marks are between 0 and 100.

 Key Points

  1. Constraints enforce data integrity and validity.

  2. Can be applied at the column level or table level.

  3. Primary key + foreign key establish relationships between tables.

  4. UNIQUE, NOT NULL, DEFAULT, CHECK help control valid input.

  5. Constraints can be added or removed using ALTER TABLE.

You may also like...