Designing Foreign Keys and Relationships the Right Way

Foreign keys are what make relational databases relational. They enforce referential integrity — ensuring that a row in one table always points to a valid row in another. Getting foreign keys right is one of the most impactful decisions in your schema design.

The Basics

A foreign key is a column (or set of columns) in one table that references the primary key of another table. When you say "each order belongs to a user," you're defining a foreign key: orders.user_id references users.id.

The database enforces this constraint automatically. You can't insert an order with a user_id that doesn't exist in the users table, and you can't delete a user who still has orders (unless you configure cascade rules).

One-to-Many Relationships

This is the most common relationship type. A user has many orders. A blog post has many comments. A department has many employees. The pattern is always the same: the "many" side gets a foreign key column pointing to the "one" side.

In ER Flow, you create this by adding a column like user_id to the orders table and drawing a relationship line. The tool handles the FK constraint for you.

One-to-One Relationships

One-to-one relationships are less common but useful for splitting a table's data for privacy, performance, or organizational reasons. For example, a users table might have a separate user_profiles table with extended information.

To enforce one-to-one, add a UNIQUE constraint on the foreign key column. This ensures that each user can have at most one profile.

Many-to-Many Relationships

When both sides can have multiple related records — students and courses, users and roles, products and categories — you need a junction table. This table has two foreign keys, one for each side, and its primary key is typically the combination of both.

For example: enrollments with student_id and course_id. You can add extra columns to the junction table, like enrolled_at or grade.

Cascade Rules

What happens when you delete a user who has orders? The ON DELETE clause controls this behavior:

CASCADE: Deleting the user automatically deletes all their orders. Use this for truly dependent data (e.g., deleting a post deletes its comments).

SET NULL: Deleting the user sets user_id to NULL on their orders. Use this when the child can exist independently.

RESTRICT (default): Prevents the deletion entirely. You must delete or reassign the orders first. This is the safest default.

Common Pitfalls

Circular references: Table A references Table B, which references Table A. This makes inserts and deletes complicated. Avoid if possible; if needed, make one side nullable.

Missing indexes: Foreign key columns should almost always be indexed. Without an index, JOIN queries and CASCADE operations can be extremely slow on large tables.

Over-referencing: Not every relationship needs a foreign key constraint. If you're referencing data across microservices or external systems, a plain ID column (without FK constraint) is often more practical.

In ER Flow, relationships are created visually. You can see every foreign key, understand the cardinality at a glance, and configure cascade rules — all without writing SQL.