MySQL - Foreign Key

MySQL Foreign Key Constraints

In relational databases like MySQL, a Foreign Key is a field (or a collection of fields) in one table that uniquely identifies a row of another table. In simpler terms, the foreign key in the ‘child’ table points to the primary key of the ‘parent’ table, creating a link between them.

This link is not just for organizational purposes; it enforces referential integrity. This crucial concept ensures that relationships between tables remain consistent. For example, a foreign key can prevent you from creating an order for a non-existent customer or deleting a customer who still has active orders.

The table containing the primary key is called the parent table (or referenced table), and the table containing the foreign key is the child table (or referencing table).

Creating a Foreign Key During Table Creation

The most common way to define a foreign key is within the CREATE TABLE statement using the FOREIGN KEY constraint.

Syntax

CREATE TABLE child_table (
    column1 datatype,
    ...,
    child_fk_column datatype,
    CONSTRAINT fk_constraint_name
        FOREIGN KEY (child_fk_column)
        REFERENCES parent_table(parent_pk_column)
);

Example

Let’s model a simple e-commerce scenario. First, we create a customers table (the parent table).

-- Parent Table: customers
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    registration_date DATE NOT NULL
) ENGINE=InnoDB;

Now, we create an orders table (the child table) with a foreign key customer_id that references the id column in the customers table.

-- Child Table: orders
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
) ENGINE=InnoDB;

Note: Both tables should use a storage engine that supports foreign keys, like InnoDB (the default in modern MySQL).

Verification

The referential integrity is now active. If you try to drop the customers table, MySQL will prevent it because the orders table depends on it.

DROP TABLE customers;

This command will fail with an error, protecting your data’s integrity:

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_orders_customers' on table 'orders'.

Adding a Foreign Key to an Existing Table

You can also add a foreign key constraint to a table that has already been created using the ALTER TABLE statement.

Syntax

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (child_fk_column)
REFERENCES parent_table(parent_pk_column);

Important: Before adding the constraint, ensure that all existing values in the child_fk_column have a corresponding match in the parent_pk_column.

Configuring ON UPDATE and ON DELETE Actions

Foreign keys can be configured to automatically handle what happens in the child table when a referenced key in the parent table is updated or deleted. This is done with the ON UPDATE and ON DELETE clauses.

• RESTRICT (Default): Prevents the update or deletion of the parent row if child rows exist.
• CASCADE: If a parent row is updated or deleted, the corresponding child rows are also updated or deleted automatically.
• SET NULL: If a parent row is updated or deleted, the foreign key column(s) in the child rows are set to NULL. This requires the foreign key column to be nullable.
• NO ACTION: A synonym for RESTRICT in MySQL.

Example with CASCADE

Let’s redefine the orders table. If a customer is deleted, all their orders should be deleted as well.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_id INT, -- Must be nullable for ON DELETE SET NULL
    amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_orders_customers_cascade
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

Dropping a Foreign Key Constraint

To remove a foreign key constraint, you need to know its symbolic name. You can find this name using SHOW CREATE TABLE child_table.

Syntax

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

After dropping the constraint, you would be able to drop the parent customers table without an error.

Primary Key vs. Foreign Key: A Comparison

Attribute	Primary Key	Foreign Key
Purpose	Uniquely identifies each record in a table.	Links a record in one table to a record in another table.
Uniqueness	Must be unique for every row.	Can contain duplicate values.
NULL Values	Cannot contain NULL values.	Can contain NULL values, unless defined as NOT NULL.
Quantity per Table	Only one primary key per table.	Multiple foreign keys can exist in a table.

Best Practices for Using Foreign Keys

• Index Your Foreign Keys: MySQL does not automatically create indexes on foreign keys. Indexing them is crucial for the performance of join queries. CREATE INDEX idx_customer_id ON orders(customer_id);
• Consistent Data Types: Ensure the foreign key column and the referenced primary key column have the exact same data type and attributes (e.g., INT UNSIGNED).
• Choose Actions Carefully: CASCADE can be dangerous as it can lead to mass deletions. Use it with caution. RESTRICT is the safest default.
• Use Naming Conventions: A clear naming convention for constraints (e.g., fk_childtable_parenttable) makes them easier to manage.

Implementing Foreign Keys in Application Code

While foreign keys are defined in the database, your application code interacts with them. Below are modern, robust examples of creating tables with foreign keys using popular programming languages.

Node.js (async/await)
Python (mysql-connector)
Java (JDBC with try-with-resources)
PHP (PDO)



#### Node.js with `mysql2/promise`
This example uses async/await for clean, modern asynchronous code.

```javascript
// Requires: npm install mysql2
const mysql = require('mysql2/promise');

async function setupDatabase() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS'
    });

    const createCustomersTable = `
      CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL
      ) ENGINE=InnoDB;`;

    const createOrdersTable = `
      CREATE TABLE IF NOT EXISTS orders (
        order_id INT AUTO_INCREMENT PRIMARY KEY,
        customer_id INT NOT NULL,
        product VARCHAR(100) NOT NULL,
        CONSTRAINT fk_orders_customers
          FOREIGN KEY (customer_id) REFERENCES customers(id)
          ON DELETE CASCADE
      ) ENGINE=InnoDB;`;

    console.log('Creating customers table...');
    await connection.execute(createCustomersTable);
    console.log('Customers table created or already exists.');

    console.log('Creating orders table with foreign key...');
    await connection.execute(createOrdersTable);
    console.log('Orders table created or already exists.');

  } catch (error) {
    console.error('Database setup failed:', error.message);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Connection closed.');
    }
  }
}

setupDatabase();

Python with mysql-connector-python

This example uses a with statement to ensure resources are managed correctly.

# Requires: pip install mysql-connector-python
import mysql.connector
from mysql.connector import errorcode

def setup_database():
    config = {
        'user': 'root',
        'password': 'password',
        'host': '127.0.0.1',
        'database': 'TUTORIALS'
    }
    try:
        with mysql.connector.connect(**config) as cnx:
            with cnx.cursor() as cursor:
                create_customers = ("CREATE TABLE IF NOT EXISTS customers ("
                                  "  id INT AUTO_INCREMENT PRIMARY KEY, "
                                  "  name VARCHAR(100) NOT NULL"
                                  ") ENGINE=InnoDB")
                cursor.execute(create_customers)
                print("Table 'customers' created or exists.")

                create_orders = ("CREATE TABLE IF NOT EXISTS orders ("
                               "  order_id INT AUTO_INCREMENT PRIMARY KEY, "
                               "  customer_id INT NOT NULL, "
                               "  product VARCHAR(100) NOT NULL, "
                               "  CONSTRAINT fk_orders_customers "
                               "  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE"
                               ") ENGINE=InnoDB")
                cursor.execute(create_orders)
                print("Table 'orders' with foreign key created or exists.")
            cnx.commit()
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Authentication error")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)
    else:
        print("Database setup complete.")

setup_database()

Java with modern JDBC

This example uses try-with-resources for automatic resource management and PreparedStatement for security.

// Add MySQL Connector/J dependency via Maven/Gradle
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateForeignKeyExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        String createCustomersSQL = "CREATE TABLE IF NOT EXISTS customers (" +
                                  "  id INT AUTO_INCREMENT PRIMARY KEY," +
                                  "  name VARCHAR(100) NOT NULL" +
                                  ") ENGINE=InnoDB;";

        String createOrdersSQL = "CREATE TABLE IF NOT EXISTS orders (" +
                               "  order_id INT AUTO_INCREMENT PRIMARY KEY," +
                               "  customer_id INT NOT NULL," +
                               "  product VARCHAR(100) NOT NULL," +
                               "  CONSTRAINT fk_orders_customers " +
                               "  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE" +
                               ") ENGINE=InnoDB;";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {

            System.out.println("Connection successful.");
            stmt.execute(createCustomersSQL);
            System.out.println("Table 'customers' created or already exists.");
            stmt.execute(createOrdersSQL);
            System.out.println("Table 'orders' with foreign key created or already exists.");

        } catch (SQLException e) {
            System.err.println("Database operation failed: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

PHP with PDO

This example uses PDO, the modern standard for database access in PHP, with error handling.

<?php
$host = '127.0.0.1';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully.\n";

    $createCustomersTable = "
      CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL
      ) ENGINE=InnoDB;";
    $pdo->exec($createCustomersTable);
    echo "Table 'customers' created or already exists.\n";

    $createOrdersTable = "
      CREATE TABLE IF NOT EXISTS orders (
        order_id INT AUTO_INCREMENT PRIMARY KEY,
        customer_id INT NOT NULL,
        product VARCHAR(100) NOT NULL,
        CONSTRAINT fk_orders_customers
          FOREIGN KEY (customer_id) REFERENCES customers(id)
          ON DELETE CASCADE
      ) ENGINE=InnoDB;";
    $pdo->exec($createOrdersTable);
    echo "Table 'orders' with foreign key created or already exists.\n";

} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>