MySQL - Primary Key

Table of content

Creating MySQL Primary Key
Creating Primary Key on Existing Column
Dropping MySQL Primary Key
Creating Primary Key Using Client Program

A PRIMARY KEY is a constraint applied on a field of a MySQL table. When this is applied, the values in that particular table column are uniquely identified. It is the most appropriate candidate key to be the main key of any table.

A table can have only one PRIMARY KEY, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a Composite Key.

You can either create a primary key while creating a new table or you can apply it on an already existing table in the database. But if it is being applied on an existing table, you must make sure that the table does not already contain a primary key and .

Creating MySQL Primary Key

To create a primary key on a new MySQL table, you must specify the column as the PRIMARY KEY while creating a new table using the CREATE TABLE statement.

Following are some points to remember while creating a Primary Key on a table −

The Primary Key column must only contain unique values.
It can not hold NULL values.
One table can have only one Primary Key.
A Primary Key length cannot be more than 900 bytes.

Syntax

Following is the syntax to define a column of a table as a primary key −

CREATE TABLE table_name(
   column_name NOT NULL PRIMARY KEY(column_name)
);

Example

In the following example, let us create a table with the name CUSTOMERS in a MySQL database using the CREATE TABLE query. In this query, we will add the PRIMARY KEY constraint on a column named ID.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) UNIQUE,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

Output

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

Field	Type	Null	Key	Default
ID	int	NO	PRI	NULL
NAME	varchar(20)	NO		NULL
AGE	int	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18, 2)	YES		NULL

Verification

To verify further that the PRIMARY KEY constraint is applied on the ID column, let us insert different types of values into the CUSTOMERS table using the following queries −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 23, 'Pune', 2000.00),
(1, 'John', 25, 'Hyderabad', 3000.00);

Following error is displayed −

ERROR 1062 (23000): Duplicate entry '1' for key 'customers.PRIMARY'

As we can see above, you cannot insert duplicate and null values into this primary key column.

Creating Primary Key on Existing Column

We can also add a primary key on an existing column of a table, if it was not created (for any reason) while creating a new table. However, adding a primary key on an existing table is only possible if the table does not already contain a primary key (as a MySQL table must not contain multiple primary keys), and the column it is being applied on must only contain unique values.

You can add the primary key on an existing table using the ALTER TABLE... ADD CONSTRAINT statement.

Syntax

Following is the syntax to create a unique constraint on existing columns of a table −

ALTER TABLE table_name 
ADD CONSTRAINT 
PRIMARY KEY (column_name);

Example

Using the ALTER TABLE statement, you can add a PRIMARY KEY on an existing column in the CUSTOMERS table created previously. In the following example, we are applying the PRIMARY KEY on the ID column as shown below −

ALTER TABLE CUSTOMERS 
ADD CONSTRAINT 
PRIMARY KEY (ADDRESS);

Output

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

Field	Type	Null	Key	Default
ID	int	NO	PRI	NULL
NAME	varchar(20)	NO		NULL
AGE	int	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18, 2)	YES		NULL

But if the column, on which the PRIMARY KEY is added, contains duplicate or null values, it cannot be set as a primary key.

Dropping MySQL Primary Key

MySQL provides the ALTER TABLE... DROP statement to drop the primary key from a table.

Syntax

Following is the syntax to drop the PRIMARY KEY constraint using the ALTER TABLE... DROP statement −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

Let us consider the CUSTOMERS table with the primary key constraint present on a column named ID. You can drop this constraint from the column ID by executing the following statement

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Output

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

Field	Type	Null	Default
ID	int	NO	NULL
NAME	varchar(20)	NO	NULL
AGE	int	NO	NULL
ADDRESS	char(25)	YES	NULL
SALARY	decimal(18, 2)	YES	NULL

Creating Primary Key Using Client Program

We can also apply a Primary Key on a table field using a client program.

Syntax

PHP NodeJS Java Python

To apply primary key on a field through a PHP program, we need to execute the CREATE query with PRIMARY KEY keyword using the mysqli function query() as follows −

$sql = 'CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)';
$mysqli->query($sql);

To apply primary key on a field through a JavaScript program, we need to execute the CREATE query with PRIMARY KEY keyword using the query() function of mysql2 library as follows −

sql = `CREATE TABLE customers(cust_ID INT NOT NULL primary key, cust_Name VARCHAR(30))`;
con.query(sql);

To apply primary key on a field through a Java program, we need to execute the CREATE query with PRIMARY KEY keyword using the JDBC function execute() as follows −

String sql = "CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)";
statement.execute(sql);

To apply primary key on a field through a Python program, we need to execute the CREATE query with PRIMARY KEY keyword using the execute() function of the MySQL Connector/Python as follows −

primary_key_query = 'CREATE TABLE TEST (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID))'
cursorObj.execute(primary_key_query)

Example

Following are the programs −

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = 'CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)';
if ($mysqli->query($sql)) {
    echo "Primary key column created successfully in customers table \n";
}
if ($mysqli->errno) {
    printf("Table could not be created!.
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Primary key column created successfully in customers table

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a table that stored primary key
  sql = `CREATE TABLE customers(cust_ID INT NOT NULL primary key, cust_Name VARCHAR(30))`;
  con.query(sql);

  //describe table details
  sql = "DESCRIBE TABLE customers";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[
    {
      id: 1,
      select_type: 'SIMPLE',
      table: 'customers',
      partitions: null,
      type: 'ALL',
      possible_keys: null,
      key: null,
      key_len: null,
      ref: null,
      rows: 1,
      filtered: 100,
      Extra: null
    }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class PrimaryKey {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Create a primary key in the customer table...!;
         String sql = "CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT PRIMARY KEY)";
         statement.execute(sql);
         System.out.println("Primary key created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE customers");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+" "
                    +resultSet.getString(3)+ " "+ resultSet.getString(4));
         }
         connection.close();
        }
         catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
Primary key created successfully...!
cust_ID int NO UNI
cust_Name varchar(30) YES 
cust_login_ID int NO PRI

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Create table 
primary_key_query = '''CREATE TABLE TEST (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY (ID))'''
cursorObj.execute(primary_key_query)
connection.commit()
print("Primary key column is created successfully!")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Primary key column is created successfully!