MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL Constraints Overview

Constraints in MySQL are rules used to limit the type of data that can go into a table, to maintain the accuracy and reliability of the data.

Here is an overview of the different types of constraints available in MySQL:

PRIMARY KEY Constraint: Uniquely identifies each record in a database table. Primary keys must contain unique values, and cannot contain NULL values. A table can have only one primary key, which may consist of single or multiple fields.

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (ID)
);

FOREIGN KEY Constraint: Used to prevent actions that would destroy links between tables. A foreign key in one table points to a primary key in another table.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

UNIQUE Constraint: Ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. However, a primary key does not allow NULL values, while a unique key does.

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    Email varchar(255) UNIQUE,
    PRIMARY KEY (ID)
);

NOT NULL Constraint: Ensures that a column cannot have a NULL value. By default, a table column can hold NULL values. The NOT NULL constraint enforces a column to not accept NULL values.

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    Email varchar(255)
);

CHECK Constraint: The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column.

CREATE TABLE Customers (
    ID int NOT NULL,
    Age int,
    CHECK (Age>=18)
);

DEFAULT Constraint: Provides a default value for a column when none is specified. That means, if you don't provide a value for the column during an INSERT operation, the default value will be used.

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    City varchar(255) DEFAULT 'Sandnes'
);

These constraints are used to enforce the data integrity in the MySQL tables. Depending on the requirements, you can apply these constraints on the table columns.

Overview of Constraints in MySQL:

Constraints define rules for the data stored in a table, ensuring consistency and accuracy.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50) NOT NULL,
  emp_department VARCHAR(50),
  emp_salary DECIMAL(10,2) DEFAULT 0,
  CONSTRAINT fk_department FOREIGN KEY (emp_department) REFERENCES departments(department_id),
  CONSTRAINT chk_salary CHECK (emp_salary >= 0)
);

How to Add Constraints in MySQL:

Add constraints during table creation or alter existing tables.

ALTER TABLE employees
ADD CONSTRAINT fk_department FOREIGN KEY (emp_department) REFERENCES departments(department_id);

List of Constraints Supported by MySQL:

MySQL supports various constraints:

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
DEFAULT

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) DEFAULT 0,
  CONSTRAINT chk_price CHECK (price >= 0)
);

Enforcing Data Integrity with Constraints in MySQL:

Constraints maintain the accuracy and consistency of data.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Creating Tables with Constraints in MySQL:

Define constraints during table creation.

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50) NOT NULL,
  age INT CHECK (age >= 0),
  UNIQUE (student_name)
);

Altering Tables to Add Constraints in MySQL:
- Modify existing tables to include constraints.
```
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (emp_salary >= 0);
```
Managing and Dropping Constraints in MySQL:
- Manage constraints using ALTER TABLE and drop with DROP CONSTRAINT.
```
ALTER TABLE employees
DROP CONSTRAINT fk_department;
```

Combining Multiple Constraints in MySQL:

Combine constraints for comprehensive data rules.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL,
  email VARCHAR(255) UNIQUE,
  CONSTRAINT chk_email CHECK (email LIKE '%@%')
);

Foreign Key Constraints in MySQL Tables:

Link tables using foreign key constraints.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Unique Constraints and Indexes in MySQL:
- Ensure uniqueness with unique constraints and indexes.
```
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_email VARCHAR(255) UNIQUE
);
```

Check Constraints in MySQL:

Define custom conditions with check constraints.

CREATE TABLE transactions (
  transaction_id INT PRIMARY KEY,
  amount DECIMAL(10,2),
  CONSTRAINT chk_amount CHECK (amount > 0)
);