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:

CREATE TABLE books (
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
book_category VARCHAR(255),
PRIMARY KEY (book_id)
);

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

If we describe the table,

DESC books;

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.

ALTER TABLE books
DROP PRIMARY KEY;

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.

ALTER TABLE books
ADD PRIMARY KEY (book_id);

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,

DESC authors;

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:

CREATE TABLE books (
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
book_category VARCHAR(255),
author_id INT,
PRIMARY KEY (book_id),
FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

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.

SHOW CREATE TABLE books;

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

ALTER TABLE books
DROP FOREIGN KEY books_ibfk_1;

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,

ALTER TABLE books
ADD FOREIGN KEY (author_id) REFERENCES authors(author_id);

Let’s DESC the books table:

DESC books;

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.

from Linux Hint https://ift.tt/3o5CzFe