intro
This blog will explain the use cases of using foreign keys within your tables inside of your database.
If you’re a regular reader of this blog, you will certainly know that we like diving deep into certain topics that may be considered off-limits for developers or DBAs making use of “vanilla” data management strategies. In this blog, we’ve covered how InnoDB interacts with its own tablespace — ibdata1 — and many other interesting things.
Another interesting thing that is on the minds of many developers and DBAs, however, are foreign keys. Foreign keys are often the source of confusion or praise — depending on who you ask. Why is that the case and what can we do to make them work for us? Let’s start from the beginning.
What are Foreign Keys?
In the database world, the concept of foreign keys refers to one or multiple columns that are used to “link” data in two tables together so that developers working on the data set can control what data gets stored in the table bearing the foreign key.
To create a table with foreign key syntax in SQL, employ such a query:
1
ALTER TABLE demo_table ADD FOREIGN KEY (fk_id_column) REFERENCES demo_table2(id);
In most cases, such an SQL query will run and complete like a breeze, successfully adding a foreign key to a table called demo_table linked to a table called demo_table2 (adjust table names as necessary.) It will run like a breeze and create a foreign key!
After the foreign key will be created, it will act as the “missing link” that links tables together. On a high level, two tables linked together with a foreign key will look like so:
The example above illustrates two tables with one foreign key entity. At the same time, keep in mind that a table can have more than one foreign key where the key references primary keys belonging to different tables. Once a foreign key relationship is established, the foreign key column in the secondary (child) table will have the same value as the corresponding row in the main table.
In other cases, you may observe an error like Can't create table database.table_name (errno: 150 "Foreign key constraint is incorrectly formed") like seen below:
Such an error can be rather confusing, but in many cases, it has to do with a character encoding and collation mismatch. If you face such an error, ensure that the character encoding and the collation are the exact same on both of the tables and restart the operation.
Foreign Key Reference Options
Before restarting the operation, though, bear in mind that foreign keys allow you to set a way that referential integrity will be preserved. Options may vary depending on your DBMS of choice, but MySQL has five options: CASCADE, SET NULL, NO ACTION, RESTRICT, and SET DEFAULT.
Here’s what those options mean and why they are so important:
In other words, you really have four options to choose from: CASCADE, SET NULL, RESTRICT, or NO ACTION . How to employ them depends heavily on the use case applicable in your specific situation: have a look at the card deck in your possession and choose wisely. Otherwise, don’t forget that create a table with foreign key syntax in SQL is only the tip of the iceberg.
We create such tables because we have a reason to do so; the reasons vary (they should), but your database needs to stay warm at all times. It needs to stay warm because you will face problems in your journey as a developer or DBA; those problems need to be squashed quickly and effectively using tools.
DbVisualizer is one of such tools. Not only will it let you observe what’s happening in your database, but it will also provide you with:
Don’t be shy — give DbVisualizer a whirl today, and until next time!
Summary
To create a table with foreign key syntax in SQL, we employ foreign keys. Foreign keys need to be applied and worked with differently depending on what database management system you find yourself using — they also have a couple of options that make them act in a way different than usual. Weigh your choices, try queries out in a sandbox if you’re in doubt, and until next time. Oh, and don’t forget to try DbVisualizer too!
FAQ
How to create a table with foreign key syntax in SQL?
To create a foreign key on the demo_table linked to the id column in table demo_table2, use this SQL query:
1
ALTER TABLE demo_table ADD CONSTRAINT fk_id_column FOREIGN KEY (id_column) REFERENCES demo_table2(id);
Where Can I Learn More About SQL Indexes?
To learn more about index types and everything else that they involve, refer to our blog over at TheTable, as well as read books and attend industry events — for those into MySQL, the book Hacking MySQL: Breaking, Optimizing, and Securing MySQL for Your Use Case is a good place to start as the book will not only tell you how to optimize your SQL queries for performance, but tell you why your databases necessitate optimization and why their performance might be faltering in the first place.
