MySQL Primary and Foreign Keys

MySQL is an RDBMS (Relational Database Management System) which is owned by the Oracle Corporation and inherited from the standard SQL. It allows access and manipulation of Databases. Whoever knows the word ‘Database’  must have knowledge of Primary and Foreign keys. There is no concept of a relational database without the existence and idea of the concepts of Primary Keys and Foreign Keys. So in this article, we are going to learn about the importance and correct use of Primary and Foreign keys in MySQL.

The primary key can be any field or column of a table, which should be a unique and non-null value for each record or a row.

The Foreign key is a field that contains the primary key of some other table to establish a connection between each other.

Let’s have a look at the syntax and different examples to create primary and foreign keys in MySQL.

Primary Keys

We can make a primary key on a segment of the table by utilizing the ALTER TABLE.

Primary key while creating a table

Suppose that we want to create a table of books in MySQL that contains the ID, name, and category of a book, in which the ID column will be the primary key.

The query for creating such a table and creating the ID column a primary key column will be like this:

In this syntax, we can define constraints at the end of the query.

If we describe the table,

We can see in the column of the key that the book_id is set as the Primary Key of the table.

Drop a Primary key

Instead of adding, if we want to delete or drop a primary key, the ALTER command is used.

So, this is all about how we can create and delete a primary key in a table.

Primary key creation via ALTER TABLE

To define a primary key, we can put the ALTER TABLE into use.

The primary key is added successfully. Now, let’s learn about the foreign keys a well.

Foreign Keys

Just like primary keys, foreign keys can be defined while defining the table using the ALTER TABLE command.

Foreign key while creating a table

In the primary key section, we have created a table for the books. Now, let’s suppose, we have another table of authors in our database that includes the author’s ID as a primary key, author’s first name, and last name,

And we want to create a foreign key to the author’s ID in the books table. So, to create a foreign key on author_id while creating the books table, we run this query:

Drop a Foreign key

Dropping a foreign is not the same as dropping a primary key. We first have to get the name of the constraints by running the “SHOW CREATE TABLE books” command.

Then provide the constraint name to the ALTER TABLE command like this:

This is how we can create and delete a foreign key in a table.

Primary key using the ALTER TABLE command

For creating a foreign key on an existing table using the ALTER TABLE command,

Let’s DESC the books table:

We can see that the author_id is set as the foreign key successfully.

Summary

We have learned about the profound and concepts of Primary keys and Foreign keys. As well as the creation, addition, and deletion of a primary or foreign key in a table.