MySQL - Composite Key

Table of content

A MySQL Composite Key is a key that consists of two or more columns in a table, used to uniquely identify a record (combination of values in the same table row). It can also be described as a Primary Key created on multiple columns.

With composite key on multiple columns of a table, a combination of these columns guarantees uniqueness, even though individually these columns may or may not guarantee uniqueness. Therefore, when the database table doesn't have any column which is individually capable of identifying a unique row (or a record) from the table, then we might need two or more two fields/columns to get a unique record/row from the table.

Creating MySQL Composite Key

To create a composite key in a MySQL table, we create a primary key on two or more columns of a table using the PRIMARY KEY keyword in the CREATE TABLE statement. The composite key must have the following features −

  • A Composite Key may or may not be a part of the Foreign key.
  • A Composite Key can not be NULL.
  • A Composite Key also can be created by combining more than one Candidate Key.
  • It is also known as Compound key.
  • All the attributes in a compound keys are foreign keys.

Syntax

Following is the syntax to create a Composite Key while creating a table −

CREATE TABLE table_name(
   column1 datatype, column2 datatype, column3 datatype..., 
   CONSTRAINT composite_key_name 
   PRIMARY KEY(column_name1, column_name2,..)
);

Example

In the following example, we are trying to create a table named CUSTOMERS and add a composite key on ID and NAME columns as shown −

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

PRIMARY KEY is added to both ID and NAME columns in the CUSTOMERS table. The combination of values inserted into these columns must be unique, even if the individual column values has duplicates.

Verification

To verify if a composite key is created or not, let us display the table definition of a CUSTOMERS table using the DESC query −

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

Dropping MySQL Composite Key

We can drop the MySQL Composite Key by using the ALTER TABLE... DROP statement.

Syntax

Following is the syntax to drop the Composite key from the column of a table −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

Using the following SQL statement, we can drop the Composite key constraint from the table −

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Verification

To verify if the Composite Key has been dropped or not, we display the CUSTOMERS table using the DESC keyword −

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

Composite Key Using a Client Program

We can also apply a Composite Key constraint on Fields to uniquely identified using a client program.

Syntax

PHP NodeJS Java Python

To apply a Composite key on fields through a PHP program, we need to execute the "Create/Alter" statement using the mysqli function query() as follows −

$sql = 'ALTER TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)';
$mysqli->query($sql);

To apply a Composite key on fields through a JavaScript program, we need to execute the "Create/Alter" statement using the query() function of mysql2 library as follows −

sql = `CREATE TABLE employee(ID Int NOT NULL, emp_Id INT NOT NULL, emp_Name varchar(25), PRIMARY KEY(ID, emp_Id))`;
con.query(sql);

To apply a Composite key on fields through a Java program, we need to execute the "Create/Alter" statement using the JDBC function execute() as follows −

String sql = "Alter TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)";
statement.execute(sql);

To apply a Composite key on fields through a python program, we need to execute the "Create/Alter" statement using the execute() function of the MySQL Connector/Python as follows −

composite_key_query = 'CREATE TABLE TEST(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, MOBILE BIGINT, CONSTRAINT CK_TEST PRIMARY KEY (ID, MOBILE))'
cursorObj.execute(composite_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.
');

//creating composite key using alter statement.
$sql = 'ALTER TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)';
if ($mysqli->query($sql)) {
    echo "composite 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 −

composite 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);

  //creating a composite key column during the table creation...!
  sql = `CREATE TABLE employee(ID Int NOT NULL, emp_Id INT NOT NULL, emp_Name varchar(25), PRIMARY KEY(ID, emp_Id))`;
  con.query(sql);

  //describe table details
  sql = "DESCRIBE TABLE employee";
  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: 'employee',
      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 CompositeKey {
   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 composite key in the customers table...!;
         String sql = "Alter TABLE customers ADD PRIMARY KEY(cust_Id, cust_Name)";
         statement.execute(sql);
         System.out.println("Composite 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...!
Composite key created successfully...!
Cust_ID int NO PRI
Cust_Name varchar(30) 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 
composite_key_query = '''CREATE TABLE TEST(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, MOBILE BIGINT, 
CONSTRAINT CK_TEST PRIMARY KEY (ID, MOBILE))'''
cursorObj.execute(composite_key_query)
connection.commit()
print("Composite key column is created successfully!")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Composite key column is created successfully!
Advertisements