MySQL Foreign Key: Syntax, Examples, and How to Use Constraints

A key is typically defined as a column or a group of columns that are used to uniquely locate table records in relational databases (including MySQL, of course). Keys help retrieve data from those tables in the most precise and effective way.

And now that we’ve covered how to use a primary key in MySQL on our blog, it’s time to give you a similarly handy guide on foreign keys—namely, give you a brief overview of what they are and tell you how to create, manage, and delete them in MySQL.

Table of contents

• What is a foreign key in MySQL?
• Why use foreign keys in MySQL?
• MySQL foreign key syntax
• MySQL foreign key example scenarios
  • One-to-many relationship example
  • Many-to-many relationship via junction table
  • Adding foreign key after table creation
• How to create foreign keys in MySQL
  • How to create a new table with foreign keys
• How to show foreign keys in MySQL
• How to drop foreign key in MySQL
• How to disable or enable foreign key checks in MySQL
  • When does disabling checks make sense
  • The risks of disabling checks
• Common MySQL foreign key errors and how to fix them
  • Error 1215: Cannot add foreign key constraint
  • Mismatched column types
  • Missing index or primary key
• Best practices for using foreign keys in MySQL
• Video guide to MySQL foreign keys
• FAQ

What is a foreign key in MySQL?

As usual, we’ll start with the definition. As we mentioned, a foreign key is a column or a group of columns that allow cross-referencing related data across tables in a database. A foreign key relationship comprises a parent table (that contains the initial column values) and a child table (with column values that reference those of the parent table). A foreign key constraint is defined on the child table.

Why use foreign keys in MySQL?

Foreign keys are not just optional features in relational design, they are essential for data reliability and long-term maintainability. Their benefits span from consistency to operational efficiency: 

• Maintain data consistency: A foreign key enforces rules that keep parent and child records synchronized. For example, you cannot assign an order to a customer that doesn’t exist. This safeguard prevents orphaned rows that otherwise create data quality issues.
• Enable cascading updates and deletes: By defining ON UPDATE and ON DELETE actions, you can automate how child data reacts when parent data changes. This ensures database logic enforces real-world rules without requiring extra application code.
• Enforce business logic at the database level: Foreign keys serve as a built-in rulebook. Whether it’s ensuring invoices always link to an existing account or that enrollments map to active students, these constraints ensure critical relationships are protected even if multiple applications interact with the same database.
• Support better schema design and troubleshooting: Clear relationships documented in constraints make databases easier to reverse-engineer, visualize, and debug. dbForge Studio can even generate ER diagrams directly from foreign key definitions.
• Optimize queries with indexes: Foreign keys automatically create indexes on child columns. These indexes accelerate lookups and joins, which is particularly important in systems with high-volume transactions. 

MySQL foreign key syntax 

Now that we know the definition and the main reasons to use foreign keys, let’s take a look at the general syntax used for defining a foreign key constraint in a CREATE TABLE or ALTER TABLE statement. 

[CONSTRAINT [constraint_name]] FOREIGN KEY 
    [index_name] (column_name, ...) 
    REFERENCES table_name (column_name,...) 
    [ON DELETE referential_action] 
    [ON UPDATE referential_action] 
 
referential_action: 
    RESTRICT | CASCADE | SET NULL | NO ACTION 

You can specify the name for your foreign key constraint in CONSTRAINT [constraint_name]. It is optional, though; in case you don’t indicate it, MySQL will generate the name for your constraint automatically. 

In ON UPDATE and ON DELETE subclauses, you can specify referential actions that will define the result of an UPDATE or DELETE operation affecting a key value in the parent table that has matching rows in the child table. Referential actions include the following: 

• CASCADE deletes or updates the row from the parent table and automatically deletes or updates the matching rows in the child table.
• SET NULL deletes or updates the row from the parent table and sets the foreign key column/columns in the child table to NULL.
• RESTRICT rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.
• InnoDB treats NO ACTION as RESTRICT; the delete or update operation for the parent table is rejected if there’s a related foreign key value in the referenced table. Meanwhile, NDB supports deferred checks, and NO ACTION specifies a deferred check; when it is used, constraint checks are not performed until commit time.
• The official MySQL documentation for FOREIGN KEY constraints also mentions the fifth action, SET DEFAULT; however, both InnoDB and NDB currently reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses. 

Now, if that looks too general, let’s take a closer look at a basic example. 

MySQL foreign key example scenarios 

Theory only goes so far. Below are practical scenarios showing how foreign keys operate in different relational structures. 

One-to-many relationship example 

In business systems, one customer can place many orders. This relationship is captured with a simple foreign key. 

Example 

CREATE TABLE customers ( 
    customer_id INT PRIMARY KEY, 
    name VARCHAR(100) NOT NULL 
); 
 
CREATE TABLE orders ( 
    order_id INT PRIMARY KEY, 
    order_number INT NOT NULL, 
    customer_id INT NOT NULL, 
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
); 

Here, the orders.customer_id column enforces that every order is tied to an existing customer. Attempting to insert an order with a customer_id not found in customers will fail, protecting referential integrity. 

Many-to-many relationship via junction table 

Some relationships are inherently many-to-many, such as students enrolling in courses. MySQL handles this by introducing a junction table with two foreign keys: 

CREATE TABLE students ( 
    student_id INT PRIMARY KEY, 
    name VARCHAR(100) NOT NULL 
); 
 
CREATE TABLE courses ( 
    course_id INT PRIMARY KEY, 
    course_name VARCHAR(100) NOT NULL 
); 
 
CREATE TABLE student_courses ( 
    student_id INT NOT NULL, 
    course_id INT NOT NULL, 
    PRIMARY KEY (student_id, course_id), 
    FOREIGN KEY (student_id) REFERENCES students(student_id), 
    FOREIGN KEY (course_id) REFERENCES courses(course_id) 
); 

This ensures that every enrollment record is valid only if both the student and the course exist. It also prevents duplicate enrollments by combining both keys into the junction table’s primary key. 

Adding foreign key after table creation 

Databases evolve, and sometimes you need to introduce a relationship after both tables already exist. The ALTER TABLE statement provides this flexibility: 

ALTER TABLE orders 
ADD CONSTRAINT fk_orders_customer 
FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id); 

This pattern allows you to retrofit referential integrity into existing schemas without recreating tables. It’s the standard approach when teams integrate legacy systems or gradually improve database design. 

How to create foreign keys in MySQL

Now let’s proceed to the actual how-to part of this article, where we’ll show you the examples of creating, showing, and dropping foreign keys. To make it even more convenient, we’ll use dbForge Studio for MySQL, our high-end IDE that addresses nearly every aspect of the development, management, and administration of MySQL and MariaDB databases—including the handling of foreign keys.

How to create a new table with foreign keys

To make it clear from the start, let’s illustrate the syntax with an easy example.

Here’s a simple MySQL foreign key example. Suppose we have a customers table with a primary key customer_id. We then create an orders table where each order references a valid customer. This ensures referential integrity between the two tables. So, when we write a CREATE TABLE statement for orders, we add a foreign key referencing the customer_id column of the customer table. This is what it looks like. 

CREATE TABLE orders ( 
    order_id int NOT NULL, 
    order_number int NOT NULL, 
    customer_id smallint UNSIGNED NOT NULL, 
    PRIMARY KEY (order_id) ); 

To create the orders table, we simply run the following query in the Studio’s SQL editor. 

How to add foreign keys to an existing table

If you already have an existing table, you may wonder how to add foreign key in MySQL without recreating the table. The answer is simple: use ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY. 

Example 

ALTER TABLE orders 
ADD CONSTRAINT fk_orders_customer_id 
FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id); 

This is what the eventual query looks like in the dbForge Studio for MySQL. 

Whichever way we choose, creating or altering, the result is the same – we have two tables linked with a foreign key.

And like we previously promised to explain, this is where the foreign key helps maintain the referential integrity of your databases. Let’s imagine that your customer table has records of 10 customers with IDs spanning 1 to 10. Now, if you attempt to enter a record in the orders table with a customer_id that does not exist in the customer table (for instance, 11), the database will reject your entry and show a warning message.

Therefore, you won’t break the said integrity by assigning an order to a customer that does not exist. And, as we said, you don’t have to store the same data in different tables simultaneously; the customer_id column in the customer table is just enough, and you simply refer to it with a foreign key.

How to show foreign keys

You might need to view the foreign key constraints defined in a database, for instance, when performing debugging or modeling the database structure. In such cases, you can run a query to let MySQL show foreign keys from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. Here is the general syntax. 

SELECT 
    table_name, 
    column_name, 
    constraint_name, 
    referenced_table_name, 
    referenced_column_name 
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
referenced_table_schema = 'database_name' 
    AND referenced_table_name = 'table_name'; 

This is what it looks like in our case. 

SELECT 
    table_name, 
    column_name, 
    constraint_name, 
    referenced_table_name, 
    referenced_column_name 
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
referenced_table_schema = 'sakila' 
    AND referenced_table_name = 'customer'; 

Note that you can also view data from related tables using the Foreign Key Lookup feature of the Studio. To do that, you need to retrieve the data of the required table, and then simply view the data of any related table directly in Data Editor. To see how it’s done, refer to Foreign Key Lookup in our documentation.

How to drop foreign key in MySQL 

Finally, there are situations when you might need to let MySQL remove foreign key constraints: for example, during schema redesign or when fixing incorrectly defined relationships. 

To remove a constraint, use the ALTER TABLE … DROP FOREIGN KEY syntax. This is the standard way to run a MySQL drop foreign key command. Once you know the constraint name, you can apply it directly in your schema changes. For instance, this is what it looks like for our case, where we want to drop the foreign key from the orders table. 

ALTER TABLE orders 
DROP FOREIGN KEY fk_orders_customer_id; 

If you’re specifically searching for how to remove foreign key constraint in MySQL, the process is the same: identify the constraint name (via SHOW CREATE TABLE or INFORMATION_SCHEMA) and then run the ALTER TABLE … DROP FOREIGN KEY command. 

How to disable or enable foreign key checks in MySQL 

By default, MySQL enforces foreign key constraints to maintain referential integrity. Every insert, update, or delete is checked to ensure that parent–child relationships remain valid. While this safeguard is essential in production, there are scenarios where temporarily disabling checks is useful — typically during bulk imports, schema migrations, or large maintenance operations. 

To let MySQL disable foreign key checks, you can use the FOREIGN_KEY_CHECKS system variable.  

Example 

-- Disable constraint enforcement 
SET FOREIGN_KEY_CHECKS = 0; 
 
-- Enable constraint enforcement 
SET FOREIGN_KEY_CHECKS = 1; 
 

Alternatively, you can use the View Server Variables window of dbForge Studio for MySQL to manage variables visually. To access it, go to the Database menu > Server Variables.

When does disabling checks make sense 

Foreign key checks should only be turned off in controlled situations where performance or flexibility outweighs strict validation. This includes scenarios such as: 

• Bulk data loads: Large imports run faster without constraint checks on every row.
• Schema migrations: Dropping and recreating tables is simpler with checks disabled.
• Re-seeding databases: Child tables can be truncated and repopulated without constraint errors. 

The risks of disabling checks 

With constraints suspended, MySQL no longer protects relationships, and that opens the door to serious issues, such as: 

• Orphaned records: Children left pointing to non-existent parents.
• Silent inconsistencies: Invalid links stored without warning.
• Application errors: Queries and joins breaking against corrupt data. 

Only disable foreign key checks in controlled situations, such as during a migration script or within a maintenance window. Re-enable them immediately after the task is complete. Leaving FOREIGN_KEY_CHECKS set to 0 in a live system will almost certainly corrupt your relational integrity over time. 

Common MySQL foreign key errors and how to fix them 

Foreign key constraints are strict by design. They prevent invalid references, but that also means small mismatches between tables often trigger errors. The most common issues fall into three categories. 

Error 1215: Cannot add foreign key constraint 

This is the most frequent error developers see when defining foreign keys. 

Causes: 

• Parent and child tables use different storage engines (e.g., MyISAM vs InnoDB).
• The referenced column in the parent is not indexed or unique.
• Data types or collations between parent and child columns do not match.
• The constraint name already exists in the schema.
• Conflicts between referential actions and column definitions (e.g., ON DELETE SET NULL on a NOT NULL column). 

How to fix: 

1. Confirm both tables use InnoDB:  

ALTER TABLE table_name ENGINE=InnoDB; 

2. Add a primary key or unique index to the parent column.
3. Make sure column types, lengths, and collations match exactly.
4. Rename the constraint if another one with the same name exists.
5. Allow NULL if using ON DELETE SET NULL. 

Mismatched column types 

Foreign keys require parent and child columns to match exactly in type and definition. Even small differences will cause errors. 

Typical issues: 

• Signed vs unsigned integers (INT vs INT UNSIGNED).
• Different VARCHAR lengths or collations.
• Inconsistent decimal precision (DECIMAL(10,2) vs DECIMAL(8,2)). 

Fix: 

• Align column types precisely on both tables.
• Use SHOW CREATE TABLE to check definitions side by side.
• Update child table definitions to match the parent. 

Missing index or primary key 

The parent column being referenced must be indexed as either a primary key or a unique key. Without it, MySQL cannot enforce the relationship. 

Broken example 

CREATE TABLE departments ( 
  dept_code VARCHAR(10)  -- no index 
); 

Fix: 

ALTER TABLE departments ADD UNIQUE (dept_code); 

Once the parent column is indexed, you can safely add the foreign key to the child table. 

By checking these three areas; storage engine, column definitions, and indexes, you can resolve nearly all MySQL foreign key errors quickly and confidently. 

Best practices for using foreign keys in MySQL 

Foreign keys can make or break the reliability of your schema. Used well, they enforce rules that keep your data consistent and your application logic simpler. Used poorly, they cause performance headaches and unexpected side effects.  

The following best practices will help you define and manage foreign keys effectively in production environments: 

• Keep definitions consistent: The parent and child columns must match exactly in data type, length, signedness, and collation. Even a subtle mismatch, such as INT vs INT UNSIGNED, will block the constraint or cause problems later.
• Always index the parent column: Foreign keys can only reference primary or unique keys. Adding proper indexes ensures both data integrity and faster joins in queries.
• Use clear and consistent names: Meaningful names like fk_orders_customers make schemas easier to navigate and migrations easier to manage. Avoid auto-generated names that hide intent.
• Apply cascading rules with care: ON DELETE CASCADE or ON UPDATE CASCADE can be powerful, but in the wrong place they can also wipe large parts of your dataset unintentionally. Use them only when the business rule clearly requires it.
• Control when checks are disabled: SET FOREIGN_KEY_CHECKS=0 is useful for bulk inserts or schema migrations, but disabling checks during normal operations is dangerous. Always re-enable them immediately after the task.
• Avoid circular dependencies: Circular references between tables make inserts, deletes, and cascades far more complex. If relationships are unavoidable, consider using a junction table to break the cycle.
• Test before production: Validate new constraints in staging with real data. This ensures constraints don’t break existing records and confirms the intended behavior before rolling out to production. 

Video guide to MySQL foreign keys

If you would like to get even more information and examples of foreign keys managed in dbForge Studio for MySQL, take a look at our comprehensive video guide.

Download dbForge Studio for a free 30-day trial today!

Besides database design and handling of foreign keys, there are a lot of features that you can explore in dbForge Studio for MySQL. You can do it for free – just download the Studio for a 30-day trial and use this time to get firsthand experience with an IDE that will definitely make your daily work with MySQL and MariaDB a breeze.

FAQ 

What happens when a foreign key constraint fails? 

MySQL rejects the operation immediately. Inserts, updates, or deletes that would break the relationship between parent and child tables do not proceed. This behavior is by design: it preserves referential integrity and prevents your database from drifting into an invalid state. 

How to remove a foreign key constraint? 

Run an ALTER TABLE statement with the DROP FOREIGN KEY clause. For example: 

ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer; 

Before doing so, confirm the exact constraint name using SHOW CREATE TABLE or by querying INFORMATION_SCHEMA.KEY_COLUMN_USAGE. 

How to fix MySQL error 1215 foreign key? 

Error 1215 usually means MySQL could not create the foreign key. The common fixes are: 

• Ensure both tables use the InnoDB engine.
• Verify that parent columns are indexed as a primary key or unique key.
• Match column definitions, including data type, length, signedness, charset, and collation.
• Resolve conflicts in referential actions (e.g., don’t use ON DELETE SET NULL on a NOT NULL column).
• By systematically checking these points, you can eliminate nearly all causes of this error. 

Can a foreign key reference a non-primary key? 

Yes, as long as the referenced column (or column set) is indexed with a unique constraint. MySQL only requires uniqueness in the parent, not necessarily a primary key. 

Can I manage foreign key constraints using dbForge GUI? 

Absolutely. dbForge Studio for MySQL allows you to add, modify, or drop foreign keys through a visual interface. It also validates column compatibility, making it harder to create invalid constraints. 

Is it possible to reverse-engineer ER diagrams with dbForge? 

Yes. dbForge Studio can generate entity-relationship diagrams automatically from your schema, displaying all foreign key relationships. This helps teams understand and document complex databases quickly. 

How can dbForge prevent foreign key constraint errors? 

The tool checks column types, indexes, and relationships as you design or modify constraints. By surfacing mismatches early, dbForge prevents the typical errors (like datatype mismatches or missing indexes) that cause constraint failures in raw SQL. 

How to view all foreign key constraints in dbForge Studio? 

Open Database Explorer in dbForge Studio, or use the Foreign Key Lookup feature in Data Editor. Both views provide a complete picture of existing constraints, including the tables and columns they connect.