MySQL foreign key examples (How to define foreign keys in MySQL)

MySQL FAQ: How do I define a foreign key in MySQL?

Answer: In this short tutorial I’ll share an example of how I typically define a foreign key in MySQL.

Diving right into an example, here’s the schema for a MySQL database table named nodes that I will link to from a second table:

create table nodes (
    id int auto_increment not null,
    uri varchar(120),
    title varchar(100),
    primary key (id)
) ENGINE = InnoDB;

In an application I just finished writing, a “node” in the Drupal CMS more or less corresponds to a single web page. So, without getting into Drupal details too much, it may help to think of a node as a “web page.”

Creating a MySQL foreign key

Second, here’s a table named logfile_records that has a foreign key named node_id that links back to the nodes table:

create table logfile_records (
    id int auto_increment not null,
    uri varchar(120),
    node_id int not null,
    page_views int,
    foreign key (node_id) references nodes(id) on delete cascade,
    primary key (id)
) ENGINE = InnoDB;

The node_id field now acts as a “link” or “pointer” back to the nodes table. This comes in very handy in all sorts of database work, as you’ll soon see.

That’s all you have to do to create a foreign key in MySQL, but because MySQL supports multiple database engines (database storage engines), you need to know if your foreign key declaration is really going to do what you think it will.

MySQL foreign key support in database engines

Laughing at myself here, there actually isn't too much to know. As of MySQL 5.x, InnoDB is the only MySQL database engine that supports foreign keys. (You might be able to use the foreign key syntax with other MySQL database engines, but InnoDB is the only database engine that actually uses the information you define in your CREATE TABLE statements.)

I thought there was more foreign key support in MySQL, but there isn't. Read the MySQL foreign key page for more information about InnoDB support, and read the MySQL database storage engines page for information about other MySQL database engines.

So, to make sure you're using the InnoDB database engine, use the syntax I showed above at the end of your MySQL create table statement, specifically this line:

) ENGINE = InnoDB;

It's important to know that InnoDB is not the default MySQL database engine, so you must explicitly add this ENGINE syntax at the end of your create table command. (MyISAM is the default MyISAM storage engine.)

MySQL foreign keys and ON UPDATE and ON DELETE

One of the most powerful things that foreign keys can give you is the ability to automatically manage your data relationships. Using my example tables above, if a node in my system is deleted, it won't make sense for me to have any logfile_records for that node. Since the original node has been deleted, why would I want to keep any secondary information about that node? That's just wasted space, and can lead to confusion at best.

When a record in the nodes table is deleted, every corresponding logfile_records record should also be deleted. And that's where the MySQL ON DELETE syntax comes in to play.

As you can see from this following line:

foreign key (node_id) references nodes(id) on delete cascade,

not only am I saying that nodeid is a foreign key, I'm also using the on delete cascade_ statement to say that whenever the original node is deleted, all records in this table that link back to the id field in the nodes table should also be deleted. That's very powerful, isn't it? Without foreign keys you have to do this sort of work manually in your SQL, and I don't care for that very much myself.

Besides the cascade action, there are other ways you can handle MySQL ON DELETE events, and these are SET NULL, NO ACTION, RESTRICT, and SET DEFAULT. I don't normally use these other constraints, so I'll just refer you to the MySQL foreign key documentation page for more information.

MySQL ON UPDATE clause: I forgot to mention it earlier, but you can use these same options with the MySQL ON UPDATE clause. I can only remember doing this a few times (whereas I use the ON DELETE action very often) but I thought I should mention it here.