MySQL PRIMARY Key

MySQL | PRIMARY Key: Learn about the MySQL PRIMARY Key, with its explanation, syntax, and query examples.
Submitted by Apurva Mathur, on September 15, 2022

PRIMARY Key

The PRIMARY key is the key that makes the record uniquely identified. If any column in the table is marked as a PRIMARY key then that column cannot contain two same values also it cannot be null. Usually, PRIMARY keys are also AI, i.e., auto-incremented, which means once initiated you don’t need to give value to the column, and it will automatically increment the number. Just like we increase the serial number, once our row is completed.

Similarly, when any key has been marked as primary key and also AI that means it will repeatedly increment and all the values will be unique.

It is important to know that, every table has one PRIMARY key, (usually IDs are kept as PRIMARY key). A single table cannot have more than 1 PRIMARY key; if you will try to implement this it will show you the error.

The PRIMARY key has always a number as its value, which means its data type would be INT or BIGINT. The PRIMARY key does not contain characters.

Let us see how we to apply the PRIMARY key constraint:

Suppose we are asked to create a table named "student_details" and inside this table, we have to make 6 columns named as, student_ID (this should be PRIMARY key as well as Auto incremented), student_name, student_department, year, gender, and marks.

So to make any column as the PRIMARY key we follow the syntax given below;

PRIMARY Key Syntax

CREATE TABLE statement table_name (
  Field1_name datatype PRIMARY KEY,
  Field2_name datatype, 
  ...
);

Now let us see how we will write the query in the situation given,

CREATE TABLE student_details(
  student_ID INT AUTO_INCREMENT PRIMARY KEY, 
  student_name VARCHAR(45) NULL, 
  student_department VARCHAR(45) NULL, 
  year INT NULL, 
  gender VARCHAR(45) NULL, 
  marks VARCHAR(45) NULL
);

This command will create a table in which "student_id" will be the PRIMARY key and AI.

Now, let us insert the value inside this table,

INSERT INTO student_details (`student_ID`, `student_name`, `student_department`, `year`, `gender`, `marks`) 
VALUES ('1', 'Rihan Jha', 'CSE', '1', 'Male', '99');