MySQL Constraints

The MySQL constraints are rules and restrictions applied to database tables to ensure the data’s integrity and maintain the relationships between tables. Constraints define the allowed values and the structure of the data within a table. Here are the most common types of constraints in MySQL:

1. Primary Key Constraint:

• Ensures that each row in a table is uniquely identified.
• Automatically enforces uniqueness and not null on the specified column(s).
• Example:
  sql CREATE TABLE students ( student_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) );

1. Foreign Key Constraint:

• Enforces referential integrity by defining a relationship between two tables.
• Ensures that values in the specified column(s) match values in a column of another table.
• Example:
  sql CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

1. Unique Constraint:

• Ensures that the values in the specified column(s) are unique across all rows.
• Multiple unique constraints can be defined on a table.
• Example:
  sql CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_email VARCHAR(100) UNIQUE, first_name VARCHAR(50), last_name VARCHAR(50) );

1. Check Constraint:

• Enforces a condition on the values in a column.
• Allows you to specify a condition using SQL expressions.
• Example:
  sql CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), price DECIMAL(10, 2), CHECK (price >= 0) );

1. Not Null Constraint:

• Ensures that a column cannot contain NULL values.
• Example:
  sql CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100) NOT NULL, email VARCHAR(100) );

1. Default Constraint:

• Specifies a default value for a column when no value is provided during insertion.
• Example:
  sql CREATE TABLE tasks ( task_id INT PRIMARY KEY, task_name VARCHAR(100), status VARCHAR(20) DEFAULT 'Pending' );

1. Auto Increment Constraint:

• Automatically generates a unique value for a column, typically used for generating primary key values.
• Example:
  sql CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), password VARCHAR(100) );

1. Index Constraint:

• Improves query performance by creating an index on one or more columns.
• Can be applied to primary key and unique constraint columns by default.
• Example:
  sql CREATE TABLE books ( book_id INT PRIMARY KEY, title VARCHAR(100), author VARCHAR(100), INDEX title_index (title) );

1. Check Constraints Using Triggers:

• MySQL does not support CHECK constraints directly, but you can use triggers to achieve similar functionality. Triggers can be used to validate data before insertion or update.

These constraints help enforce data integrity and maintain relationships between tables in a MySQL database. The choice of constraints depends on the specific requirements of your application and the data model you are designing.