In this tutorial, we’ll learn what a primary key is, How to create a primary key using CREATE table or using ALTER table statement, and how to drop a primary key using MySQL.
Also read: MySQL CREATE TABLE – How to create a table in MySQL?
Introduction to MySQL Primary Key
The concept of keys in SQL is to uniquely identify the rows or records from a table. Keys are also used to establish relationships between two or more tables.
A primary key is a key that consists of one or more attributes(minimal set) to identify each and every record present in the table uniquely.
There are two properties for an attribute or group of minimal attributes to be declared as a primary key:
Unique: In a table, if a column is unique then each and every value of that column must be unique, which means the value cannot be repeated in the same column. For example, a Serial number of products, Roll number of students, ISBN for books, etc. All these values are uniquely identifiable and therefore can become a primary key for a table.
NOT NULL: A primary key is unique but just a uniquely identifiable values in a column cannot be considered as a primary key because the primary key is used for uniquely identifying the records and if, in case, we let the values of that column remain null then it won’t be able to identify the records uniquely which in turn means that we are violating the primary key constraint. Therefore, a primary key must always be not null.
We can create the primary key while creating a table or while altering a table. When we don’t have any attribute which can be constituted as a primary key then in this case we can create an auto-incrementing column that will be used to uniquely identify the values and will become the primary key for that table.
Primary key while creating a table
Let’s take an example, we’ll create a table of students which will have values such as Roll Number, First Name, Last Name, Email, Phone Number. Here, we can make roll number a primary key as it will be unique and will never be null as each and every student is assigned one roll number which is unique to the student.
Let’s see the statement below:
CREATE TABLE student (
roll_number INT,
fname VARCHAR(40),
lname VARCHAR(40),
email VARCHAR(60),
phone INT,
PRIMARY KEY (roll_number)
);Code language: SQL (Structured Query Language) (sql)Primary key while altering a table
When we already have a table but we haven’t assigned any column to be a primary key then we can use ALTER table statement to create a primary key. Let’s take the example of the student table only and we’ll mark the Roll number as our primary key.
Let’s see the statement below:
ALTER TABLE student
ADD PRIMARY KEY (roll_number);Code language: SQL (Structured Query Language) (sql)Default Primary Key
When we do not have any column or a minimal combination of columns that can be constituted as unique and not null. Then, in this case, we use a column that is numeric and auto-incremented. For example, we are creating a database for hospital visitors which consists of First name, Last Name, Date of visit, Time of visit, Contact. Now, a person can come to visit several times and we need to keep the records. In this case, we cannot create a primary key with the existing data as all the values might repeat, therefore, we use auto-increment column which will become the primary key of this table.
See the following example:
CREATE TABLE visitors (
Visitor_ID int NOT NULL AUTO_INCREMENT,
fname varchar(50),
lname varchar(50),
dov DATE,
tov TIME,
Contact int,
PRIMARY KEY (Visitor_ID)
);Code language: SQL (Structured Query Language) (sql)Drop a Primary key
To drop a primary key, you can use the below statement. We’ll be using the student table example only.
ALTER TABLE student
DROP PRIMARY KEY;Code language: SQL (Structured Query Language) (sql)Conclusion
In this MySQL primary key tutorial, we’ve seen how to create and manage primary keys in our database tables. Primary keys are an important part of any relational database, as they help to ensure the integrity of the data. By taking the time to understand how primary keys work, and by following the tips in this tutorial, you can make sure your data is safe and secure.
Do you have any questions about primary keys, or about the techniques that we’ve used in this tutorial? Let us know in the comments section below!
Useful Resources: https://dev.mysql.com/doc/refman/8.0/en/primary-key-optimization.html