Foreign key constraint demo
The goal is to create a dropdown selection in the 'navbar' table for navbar category, linked to it's own 'navbar category' table
Let's examine the steps for setting this up.
The final result will look like this.
Inserting a new row in 'Navbar':
'Navbar' > Structure - showing the foreign key in row 2, and at the bottom under 'indexes'.
To add the constraint:
Structure > Relation View
Browsing the 'Navbar' table. The foreign key in column 2.
This means we're only holding that value in this table; the unique id of a row in a different table, 'navbarcategories'. The details of that row arent important here. We just need a hook.
That hook allows the PHPAdmin UI that we're working in to give more detail about which row its referring to, in this case, the name of the navbar category that the row id is linked to.
Within PHPMyAdmin as shown here, the UI does some extra querying in order to show us the id# and the name of the category, resulting in meaninful data displayed to us upon which we can act. Later in PHP we can similarly query various tables this way, to build a complete record of something.
Again- the only value from the 'navbarcategory' table that we keep track of in the 'navbar' table is the idNavCategory. We then take that key over to it's origina table for more info. That is the idea here.
Foreign key = another table's primary key.
The constraint comes in when we disallow deletion or changes. Those actions can break the whole logic of an app. The default is 'restrict'.
and there are additional options:
To show this in action I'll attempt to delete for id#26 from the navbar categories table.
The constraint stops the action to protect the db integrity.
squirrelworks