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 FOREIGN KEY: Foreign Key Constraints

The FOREIGN KEY in MySQL is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table containing the FOREIGN KEY is called the child table, and the table containing the PRIMARY KEY is called the referenced or parent table.

Prerequisites:

A MySQL server up and running.
Access to a MySQL user account with privileges to create and modify tables.

Tutorial:

Connect to the MySQL server:

To start the mysql command-line client, open a terminal or command prompt, and enter:

mysql -u [username] -p

Replace [username] with your MySQL username and enter your password when prompted.

Select a database:

Select the database where you want to create your tables:

USE [database_name];

Replace [database_name] with the name of your database.

Create a parent table:

Create a users table with an id column as the PRIMARY KEY:

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(255),
    PRIMARY KEY (id)
);

Create a child table with a FOREIGN KEY:

Create an orders table with a user_id column as the FOREIGN KEY that refers to the id column in the users table:

CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    user_id INT,
    product VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Insert data into the parent table:

Insert a user into the users table:

INSERT INTO users (username) VALUES ('johndoe');

Insert data into the child table:

Insert an order into the orders table for the user you just created. The user_id must match the id of an existing user:

INSERT INTO orders (user_id, product) VALUES (1, 'Product 1');

Attempt to insert invalid data:

If you try to insert an order with a user_id that doesn't exist in the users table, MySQL will reject it because of the FOREIGN KEY:

INSERT INTO orders (user_id, product) VALUES (999, 'Product 2');
-- This will fail because there is no user with id 999

Exit the MySQL command-line client:

EXIT;

By using FOREIGN KEYs in MySQL, you can ensure the integrity of the data in your tables. This is an important concept in relational databases and is key to avoiding inconsistent or invalid data in your database.

How to create FOREIGN KEY constraints in MySQL:

Use the FOREIGN KEY keyword during table creation to establish relationships.

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

Adding foreign key constraints to existing tables in MySQL:
- Alter existing tables to add FOREIGN KEY constraints.
```
ALTER TABLE order_items
ADD FOREIGN KEY (product_id) REFERENCES products(product_id);
```

MySQL FOREIGN KEY examples:

Examples of FOREIGN KEY constraints with multiple tables.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50)
);

CREATE TABLE tasks (
  task_id INT PRIMARY KEY,
  emp_id INT,
  task_description VARCHAR(255),
  FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

Enforcing referential integrity with FOREIGN KEY in MySQL:

FOREIGN KEY constraints maintain data integrity between related tables.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50)
);

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

CASCADE and SET NULL actions with MySQL FOREIGN KEY:

Define actions to take when referenced records are updated or deleted.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE SET NULL
);

Disabling and enabling FOREIGN KEY constraints in MySQL:
- Temporarily disable and enable FOREIGN KEY constraints.
```
-- Disable
SET foreign_key_checks = 0;

-- Enable
SET foreign_key_checks = 1;
```

MySQL FOREIGN KEY and ON DELETE options:

Specify actions to be performed when referenced records are deleted.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

FOREIGN KEY and ON UPDATE actions in MySQL:

Define actions to take when referenced records are updated.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50)
);

CREATE TABLE tasks (
  task_id INT PRIMARY KEY,
  emp_id INT,
  task_description VARCHAR(255),
  FOREIGN KEY (emp_id) REFERENCES employees(emp_id) ON UPDATE SET NULL
);

Checking existing FOREIGN KEY constraints in MySQL:

Query information_schema to inspect existing FOREIGN KEY constraints.

SELECT
  constraint_name,
  table_name,
  column_name,
  referenced_table_name,
  referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'your_database';

Foreign key constraints vs triggers in MySQL:

Understand the differences and choose between FOREIGN KEY constraints and triggers.

-- Example of using a trigger instead of a foreign key constraint
CREATE TRIGGER check_customer_exists
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.customer_id NOT IN (SELECT customer_id FROM customers) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Customer does not exist';
  END IF;
END;

MySQL FOREIGN KEY with multiple columns:

Define FOREIGN KEY constraints involving multiple columns.

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Composite foreign keys in MySQL:

Use multiple columns as a composite foreign key.

CREATE TABLE employee_projects (
  emp_id INT,
  project_id INT,
  PRIMARY KEY (emp_id, project_id),
  FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
  FOREIGN KEY (project_id) REFERENCES projects(project_id)
);