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:
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,
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.
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.
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,
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:
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.
Then provide the constraint name to the ALTER TABLE command like this:
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,
ADD FOREIGN KEY (author_id) REFERENCES authors(author_id);
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.
