Foreign key — a column that references the primary key of another table

Beginner5 min read·eng-15-008

sql

Concept

Foreign key — a column (or set of columns) in one table that references the primary key of another table. Enforces referential integrity: you can't have an order for a non-existent user.

Referential integrity: The database guarantees that the referenced row always exists. If orders.user_id is a foreign key referencing users.id, the database prevents:

Inserting an order with a user_id that doesn't exist in users.
Deleting a user who has existing orders (unless a cascade rule applies).

Terms:

Parent table (referenced): users — has the primary key.
Child table (referencing): orders — has the foreign key.

Cascade actions — what happens when the parent row changes:

ON DELETE CASCADE: Delete child rows automatically when parent is deleted. DELETE FROM users WHERE id = 1 → all orders for user 1 are also deleted.
ON DELETE SET NULL: Set the FK column to NULL when parent is deleted. Good for optional relationships.
ON DELETE RESTRICT (default): Prevent the parent row from being deleted if children exist. Throws an error.
ON UPDATE CASCADE: Update the FK value in child rows when parent PK changes (rare with auto-increment IDs).

Performance: A foreign key constraint adds an index on the FK column automatically in most DBs (MySQL InnoDB). This is required to check existence efficiently.

To index or not: Some teams disable FK constraints for performance (high-volume inserts). The referential integrity is then enforced at the application level. Dangerous but sometimes necessary for bulk loads.

MySQL vs PostgreSQL: MySQL InnoDB supports FK constraints. MyISAM does NOT. PostgreSQL always supports them with deferred constraints.

Code Example

php

<?php
// CREATING FOREIGN KEYS in Laravel migrations

// Method 1: fluent shorthand
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')     // BIGINT UNSIGNED NOT NULL
          ->constrained()            // references users(id) — table name auto-inferred
          ->onDelete('cascade');     // ON DELETE CASCADE
});

// Method 2: explicit
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id');
    $table->foreign('user_id')
          ->references('id')
          ->on('users')
          ->onDelete('restrict')     // prevent user deletion if orders exist
          ->onUpdate('cascade');
});

// Method 3: cascade soft deletes instead of DB cascade
// (common in Laravel — lets you use withTrashed() on soft-deleted parents)
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->nullOnDelete(); // SET NULL on delete
});

// Referential integrity in action
\DB::table('orders')->insert(['user_id' => 9999, 'total' => 100]); // FAILS!
// Illuminate\Database\QueryException: Cannot add or update a child row:
// a foreign key constraint fails (orders.user_id → users.id)

User::find(1)->delete(); // FAILS if orders exist with onDelete('restrict')
// Illuminate\Database\QueryException: Cannot delete or update a parent row

// DROPPING a foreign key
Schema::table('orders', function (Blueprint $table) {
    $table->dropForeign(['user_id']); // drops foreign key constraint
    $table->dropColumn('user_id');
});

// Disabling FK checks (for seeding or bulk loads)
\DB::statement('SET FOREIGN_KEY_CHECKS=0');   // MySQL
// ... bulk insert ...
\DB::statement('SET FOREIGN_KEY_CHECKS=1');

// Checking FK integrity after bulk load
\DB::statement("
    SELECT o.id FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
    WHERE u.id IS NULL
"); // finds orphan orders (broken referential integrity)

PreviousCardinality — the number of unique values in a column (affects index choice)Vocabulary — Database & SQL NextConstraint — a rule the database enforces on data (NOT NULL, UNIQUE, CHECK)Vocabulary — Database & SQL