MySQL foreign key-how to define a foreign key in MySQL

Table of Contents 

• Creating a MySQL foreign key
• MySQL foreign key support in database engines
• MySQL foreign keys and on UPDATE and on DELETE

MySQL foreign Key Faq:how do I define a foreign key in MySQL?

Answer:here ' s A quick example of how I typically define a foreign key in MySQL.

Diving right into an example, here's the definition for a MySQL database table named that nodes I'll link to from a secon D Table:

CREATE TABLE nodes (    ID int auto_increment NOT NULL,    URI varchar,    title varchar,    primary KEY (i d) ENGINE = InnoDB;

　　

In an application I just finished writing, a ' node ' in the Drupal CMS + or less corresponds to a single Web page. So, without getting into Drupal details too much, it could help to think of a node as a ' Web page '.

Creating a MySQL foreign key

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

CREATE TABLE logfile_records (    ID int auto_increment NOT NULL,    URI varchar (+),    node_id int not NULL,    PA ge_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 the database work (as you'll see soon).

That's all of you has to does to the 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 are really going to does what you think it'll.

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 being able to use the foreign key syntax with other MySQL database engines, but InnoDB was the only database engine That's actually uses the information you define in your CREATE TABLE statements.)

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

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

) ENGINE = InnoDB;

　　

It's important to know that InnoDB are not the default MySQL database engine and so are 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 this foreign keys can give you are the ability to automatically manage your data relationsh Ips. Using My example tables above, if a node in the My system is deleted, it won ' t do sense for me has any for that logfile_records . Since The original node has been deleted, what would I want to keep any secondary information on that node? That ' s just wasted space, and can leads to confusion at best.

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

As can see from this following line:

Foreign KEY (node_id) references nodes (ID) on DELETE cascade,

　　

Not only am I saying nodeid so is a foreign key, I ' m also using the " on" Delete Cascade_ statement to say that whe Never the original node is deleted, all records in this table, 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 had 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 is other ways your can handle MySQL on DELETE events, and these is SET NULL, N O ACTION, RESTRICT, and SET DEFAULT. I don ' t normally use these other constraints, so I'll just refer the MySQL Foreign Key documentation page for more Information.

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

MySQL foreign key-how to define a foreign key in MySQL