We spoke about normalization but never fully covered primary and foreign keys. It’s not something that’s unique to MySQL, but it’s something that you hear quite frequently: primary key and foreign key. We have already looked at an example of this in the previous MySQL article, but I wanted to elaborate on it a little further.

https://blog.devgenius.io/php-p93-mysql-normalization-7e9a9809311e

What is a Primary Key in SQL?

It’s just a column or combination of columns inside of your table that is unique for each of the entries within that table. A quick example is with a person’s social security number. With the government, your primary key is your SSN. It’s unique only to you. If we added individuals to a table, the SSN could be used as a primary key.

It doesn’t have to be something concrete like that. It can be an arbitrary number. Most frequently, an ID column is assigned to each record. Every time you add a new entry, the ID column is incremented. That means that each record will have a unique value for the ID column making it a prime candidate for a primary key. So, the primary key must be unique for each record and cannot be null.

What is a Foreign Key?

This is where relationships begin to thrive in relational databases. The foreign key is used to define relationships between tables. Let’s take a look at an example before going any further.

We created three tables inside of our database:

• users
• customers
• customer_types

Our users table contains a primary key, id. So does our customer_types table. The customers table also contains a primary key, id. But it also contains two additional fields. The first field, user_id is the foreign key. The foreign key states that there is a primary key in another table that matches the value of this foreign key. By looking at the user_id column, we can quickly gauge that this foreign key is pointing to the id primary key in the users table. This is what allows the linking of one table to another. We can now link our customers table to the users table.

We can also link our customers table to our customer_types table. The customer_type_id foreign key will point to the id primary key in the customer_types table.

You might be able to see that foreign keys do not have to be unique. For example, our customers table might link a user to multiple customer types. For example, one user may be a customer that’s a distributor and a customer that ships directly to the consumer.

The user, however, shouldn’t belong to the distributor customer type more than once. We can restrict that by making the combination of user_id and customer_type_id act as a primary key. Since we have that primary key combination, we can remove the id column from the customers table since we’ll have our primary key.

Just remember that foreign keys link data from one table to another.

Making Multiple Columns Act as a Primary Key

We’ll need to alter our table to make multiple fields function as a primary field. We can do that with the following command:

ALTER TABLE `customers` ADD PRIMARY KEY(`user_id`, `customer_type_id`);

Alternately, we can do it through phpMyAdmin.

• Open phpMyAdmin
• Go to your table. In this instance it’s customers
• Click the Structure tab
• Select the two columns and click on the Primary button

You’ll see the PRIMARY keyname added at the bottom under indexes.

Foreign Key Constraints

Aside from just referencing a table, you can add foreign key constraints as well. We’ll tackle both below. We want to restrict deletion and updates. That means that if we delete or update a value in the parent table, this will prevent it because there’s a constraint.

We can change the constraints to:

• No Action: if we make a change in the parent, the action fails
• Cascade: if a row is deleted or modified in the parent table, all of the rows associated with that table are also deleted in the child table
• Set Null: if deleted or modified in the parent table, set all of the values to null in the child table
• Restrict: does not allow deleting or modifying the parent without removing all of the dependent records first.

We can add relationships through SQL.

ALTER TABLE `customers` 
    ADD CONSTRAINT `user_FK` 
    FOREIGN KEY (`user_id`) 
    REFERENCES `users`(`id`) 
    ON DELETE RESTRICT 
    ON UPDATE RESTRICT;

Or we an do this through phpMyAdmin.

• Open phpMyAdmin
• Select the table that you want to add the foreign key constraints to.
• Select the Structure tab
• Click on the Relational View
• Add a constraint name, like user_FK
• Select the column: user_id
• Select the database: dino_test_db
• Select the table that user_id points to: users
• Select the column that’s pointed to within the users table: id
• Click Save

We can repeat the process for customer_type_id.