In Adminer, the tool we use to manage our databases, it is relatively simple to create relational tables, but you must follow a procedure.
For this work, you must create the tables that have reference to them in your system first. To follow the language we introduced above, we will call this table the reference table. Every table that refers to another table through a foreign key, a referring table, can only be created if the reference table exists. You cannot have a foreign key to a non-existing table. Pretty logical.
In our example, we would need to create the artists table first (that’s the reference table), then the albums table because albums refers to artists (that’s the referring table).
Note that you do not need to create these tables. I wrote this to explain the process to you, but it is not an assignment.
So the creation of the artists table would look like this:
Once you have done the reference table(s), you can create the referring table(s). In our example, the albums table would look like this:
Notice that the data type for the artist column is the name of the artists table. (left arrow) This is how Adminer manages foreign keys. All the tables in your database that have a primary key setup (in our case the id column) appear at the bottom of the data type drop menu. To establish the connection, you select the name of the table you want to use as foreign key as the data type of the column where the reference happens.
On the same screenshot, you will notice that there is an option setup for the artist column as well. (right arrow) This is to tell MySQL what to do if ever you delete an artist in the artists table and there are references to its id in some entries of the albums table. The same as you cannot have a foreign key to a non-existing table, you also cannot have a reference to a non-existing item in a reference table. In this case, we select the CASCADE option which means that if you delete an artist, every album of this artist will also be deleted.
After creating a table that contains a foreign key reference, you will see it documented in the table’s structure screen in Adminer. This is a good way of confirming that you did it properly. It looks like this:
When you create the tables for a project that has relational tables, make sure you pay attention to the following elements:
- Create the reference table first, then the referring table with the foreign key.
- Set the id columns of every table to Auto Increment.
- Pick the reference table as data type for the relational column in the referring table.
- Confirm that the foreign key is registered by looking at the referring table’s structure in Adminer.
That’s it for today. I did say it was a “very simple and short tutorial”, didn’t I?