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
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 −
$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!
”;