What is a Foreign Key?

A foreign key is a field in one table that refers to the primary key in another table. It creates a link between the two tables and ensures data integrity (meaning the database prevents invalid references, like a post pointing to a non-existent user).

In our case:

  • The posts table has a user_id field.
  • This field references the id field in the users table.
  • This means “every post must belong to a valid user”.

Creating the Posts Table with a Foreign Key

Let’s create our posts table with a foreign key relationship to users. In Workbench, go to the query editor and run the script below. If you created the posts table already you can run DROP table posts;.

CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  user_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Breaking down each part:

id INT PRIMARY KEY AUTO_INCREMENT

  • Same pattern as the users table - unique identifier for each post.

title VARCHAR(200) NOT NULL

  • Post title, required field (NOT NULL means it cannot be empty).

content TEXT

  • Post content, can be long text (TEXT allows more characters than VARCHAR).

user_id INT NOT NULL

  • References which user wrote this post.
  • NOT NULL ensures every post has an author.

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

  • Automatically records when the post was created.
  • DEFAULT CURRENT_TIMESTAMP means MySQL fills this in automatically.

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

This line creates the actual relationship between tables and enforces data integrity. Let’s break this down:

  • FOREIGN KEY (user_id): Declares that the user_id column in the posts table is a foreign key.
  • REFERENCES users(id): Specifies that this foreign key points to the id column in the users table.
  • What this means: Every value in posts.user_id must match an existing value in users.id.
  • Database protection: The database will reject any INSERT or UPDATE that tries to create a post with a user_id that doesn’t exist in the users table.
  • ON DELETE CASCADE: When a user is deleted, all their posts are automatically deleted too (prevents orphaned data).

Understanding Database Constraints

Constraints are rules that the database enforces to maintain data integrity and prevent invalid data from being stored.

Our posts table uses several types of constraints:

  • NOT NULL: Prevents empty values (title and user_id cannot be empty).
  • PRIMARY KEY: Ensures each record has a unique identifier (id field).
  • FOREIGN KEY: Maintains relationships between tables (user_id must reference a valid user).
  • DEFAULT: Provides automatic values when none are specified (created_at gets current timestamp).

These constraints work together to ensure our data remains consistent and valid. The database will reject any operation that violates these rules.

Verify Your Table

Check that your table was created correctly:

DESCRIBE posts;

You should see all the fields listed with their data types and constraints.

Tags: