Below we will show some simple examples of how to set up foreign keys and how they work in MySQL. In our examples, we will reference the parent and child tables below. The tables labelled “Customer” and “Contact” represent the parent and child tables, respectively.
There are basically two ways to define foreign keys in MySQL:
In addition to these examples, we have also included a discussion of what foreign key constraints are and how they are formatted, with examples as well.
Just In Case — What’s A Foreign Key?
A foreign key is a field (or a set of fields) in a table that uniquely identifies a row of another table. The table in which the foreign key is defined is called the “child table” and it (often) refers to the primary key in the parent table.
Foreign key constraints can then be used to define how data integrity is enforced between two tables (e.g., when a table row is deleted or updated).
If done correctly, the link between the primary key and the foreign key will always be maintained, so your database will never be left with orphaned records in the child table (e.g., leftover contact information tied to a removed customer). The following simplified (and not fully-normalized) customer database example helps us visualize these foreign key concepts.
1. Defining Foreign Keys with CREATE TABLE
Go into the MySQL console by typing the mysql command with the correct user and password arguments. If needed, type “man mysql” to get more information.
mysql -u -p
Create a database and start using it:
CREATE DATABASE testdb; use testdb;
Creating the Tables
Now create the two tables:
CREATE TABLE customer ( id INT NOT NULL AUTO_INCREMENT, firstname varchar(50) NOT NULL, lastname varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE contact ( id INT, customer_id INT, info varchar(50) NOT NULL, type varchar(50) NOT NULL, INDEX par_ind (customer_id), CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=INNODB;
Verifying the Table Structure
Let’s see what our database structure looks like:
SHOW TABLES; +——————+ | Tables_in_testdb | +——————+ | contact | | customer | +——————+ DESCRIBE customer; +———–+————-+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra | +———–+————-+——+—–+———+—————-+ | id | int(11) | NO | PRI | NULL | auto_increment | | firstname | varchar(50) | NO | | NULL | | | lastname | varchar(50) | NO | | NULL | | +———–+————-+——+—–+———+—————-+ DESCRIBE contact; +————-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————-+————-+——+—–+———+——-+ | id | int(11) | YES | | NULL | | | customer_id | int(11) | YES | MUL | NULL | | | info | varchar(50) | NO | | NULL | | | type | varchar(50) | NO | | NULL | | +————-+————-+——+—–+———+——-+
Note the MUL value in the Key column of the contact table. This tells us the customer_id field is the first column of a non-unique index (meaning it can have multiple rows with the same value).
Adding Data to the Table
Now we can fill our two database tables with some sample data. First, the customer table:
INSERT INTO customer (firstname, lastname) VALUES (‘Elaine’, ‘Stevens’), (‘Mary’, ‘Dittman’), (‘Skip’, ‘Stevenson’);
Our customer table now looks like this:
SELECT * FROM customer; +—-+———–+———–+ | id | firstname | lastname | +—-+———–+———–+ | 1 | Elaine | Stevens | | 2 | Mary | Dittman | | 3 | Skip | Stevenson | +—-+———–+———–+
Some sample data for the contact table:
INSERT INTO contact (customer_id, info, type) VALUES (‘1’, ‘111–111–111’, ‘work’ ), (‘1’, ‘111–111–123’, ‘home’), (‘1’, ‘email@example.com’, ‘email’), (‘2’, ‘222–222–222’, ‘work’), (‘2’, ‘firstname.lastname@example.org’, ‘email’), (‘2’, ‘email@example.com’, ‘email’), (‘3’, ‘firstname.lastname@example.org’, ‘email’);
Our contact table now looks like this:
SELECT * FROM contact; +——+————-+——————–+——-+ | id | customer_id | info | type | +——+————-+——————–+——-+ | NULL | 1 | 111–111–111 | work | | NULL | 1 | 111–111–123 | home | | NULL | 1 | email@example.com | email | | NULL | 2 | 222–222–222 | work | | NULL | 2 | firstname.lastname@example.org | email | | NULL | 2 | email@example.com | email | | NULL | 3 | firstname.lastname@example.org | email | +——+————-+——————–+——-+
Now that we have the sample data, let’s see how foreign keys help preserve data integrity.
Deleting Referential Data (to See How Data Integrity is Preserved)
Earlier we defined the foreign key constraint as:
“FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE”
This means that when we delete a specific customer, the related contact table rows should also be deleted. Also, ON UPDATE CASCADE will cascade any updates on the parent table to referenced fields in the child table (in this case, customer_id).
Let’s first test deleting a customer:
DELETE FROM customer WHERE firstname = ‘Skip’;
When we look at our tables again, we see that both tables were changed as required, since all the contact rows for Skip were also removed:
SELECT * FROM customer; +—-+———–+———-+ | id | firstname | lastname | +—-+———–+———-+ | 1 | Elaine | Stevens | | 2 | Mary | Dittman | +—-+———–+———-+ SELECT * FROM contact; +——+————-+——————–+——-+ | id | customer_id | info | type | +——+————-+——————–+——-+ | NULL | 1 | 111–111–111 | work | | NULL | 1 | 111–111–123 | home | | NULL | 1 | email@example.com | email | | NULL | 2 | 222–222–222 | work | | NULL | 2 | firstname.lastname@example.org | email | | NULL | 2 | email@example.com | email | +——+————-+——————–+——-+
Updating Referential Data (to See How Data Integrity is Preserved)
Now let’s update the customer_id of Elaine, to test the ON UPDATE CASCADE:
UPDATE customer SET id=7 WHERE firstname=’Elaine’;
The needed changes have now been made to both tables:
select * from customer; +—-+———–+———-+ | id | firstname | lastname | +—-+———–+———-+ | 7 | Elaine | Stevens | | 22 | Mary | Dittman | +—-+———–+———-+ select * from contact; +——+————-+——————–+——-+ | id | customer_id | info | type | +——+————-+——————–+——-+ | NULL | 7 | 111–111–111 | work | | NULL | 7 | 111–111–123 | home | | NULL | 7 | firstname.lastname@example.org | email | | NULL | 22 | 222–222–222 | work | | NULL | 22 | email@example.com | email | | NULL | 22 | firstname.lastname@example.org | email | +——+————-+——————–+——-+
2. Defining Foreign Keys with ALTER TABLE
It is also possible to add foreign keys to tables after they have already been created. Instead of doing the table creation in one step, as we did before, we can also do it in multiple steps.
Creating & Then Altering the Tables
CREATE TABLE customer ( id INT NOT NULL AUTO_INCREMENT, firstname varchar(50) NOT NULL, lastname varchar(50) NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE contact ( id INT, customer_id INT, info varchar(50) NOT NULL, type varchar(50) NOT NULL ) ENGINE=INNODB; ALTER TABLE contact ADD INDEX par_ind ( customer_id ); ALTER TABLE contact ADD CONSTRAINT fk_customer FOREIGN KEY ( customer_id ) REFERENCES customer ( id ) ON DELETE CASCADE ON UPDATE RESTRICT;
Note that you can’t change an existing foreign key. You will first have to remove the existing foreign key by its symbol name and then you can define a new foreign key as we did previously.
ALTER TABLE contact DROP FOREIGN KEY fk_customer;
Final Words on Foreign Keys
Foreign keys are integral to good database management. If you are now interested in looking at more advanced examples of their use, see the Examples of Foreign Key Clauses section here.
On that page is an example in which a “product_order” table has foreign keys for two other tables. One foreign key references a two-column index in the “Product” table. The other references a single-column index in the “Customer” table.
Other interesting foreign key examples can be found here.
Addendum: Foreign Key Constraints
Foreign key constraints are used to keep the data in your tables consistent when either removing (ON DELETE) or updating (ON UPDATE) table row data.
The syntax for a foreign key constraint definition in a CREATE TABLE or ALTER TABLE statement looks like this:
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_column_name, …) REFERENCES parent_table_name (index_column_name,…) [ON DELETE reference_option][ON UPDATE reference_option]
The “CONSTRAINT [symbol]” part is optional and can be used to define a name for the foreign key. This is useful for removing foreign keys by their symbol name and also for producing clearer error logging.
The index_name value represents a foreign key ID and is ignored if there is already an explicitly defined index on the child table that can support the foreign key. One or more columns can be used to uniquely identify the foreign key.
Used by both ON DELETE and ON UPDATE, reference_option can point to one of the following:
This command deletes or updates the row from the parent table, and automatically deletes or updates the matching rows in the child table.
This rejects the DELETE or UPDATE operations for the parent table. Specifying RESTRICT is the same as omitting the ON DELETE or ON UPDATE clause. In other words, rejection is the default action in MySQL.
A keyword from standard SQL, NO ACTION is essentially equivalent to the RESTRICT command in MySQL.
Finally, the SET NULL command deletes or updates the row from the parent table, and sets the foreign key column (or columns) in the child table to NULL. This action can be useful in situations where the rows in the child table should not be deleted when the parent table row is removed.
Photo Sources: www.mvpcoo.com
HostingAdvice.com is a free online resource that offers valuable content and comparison services to users. To keep this resource 100% free, we receive compensation from many of the offers listed on the site. Along with key review factors, this compensation may impact how and where products appear across the site (including, for example, the order in which they appear). HostingAdvice.com does not include the entire universe of available offers. Editorial opinions expressed on the site are strictly our own and are not provided, endorsed, or approved by advertisers.
Our site is committed to publishing independent, accurate content guided by strict editorial guidelines. Before articles and reviews are published on our site, they undergo a thorough review process performed by a team of independent editors and subject-matter experts to ensure the content’s accuracy, timeliness, and impartiality. Our editorial team is separate and independent of our site’s advertisers, and the opinions they express on our site are their own. To read more about our team members and their editorial backgrounds, please visit our site’s About page.