Multiple Primary Keys in MySQL

1. Create Table Using student_details_table in MySQL
2. Use the DESCRIBE Statement Shows the Structure of Table in MySQL

In this tutorial, Our goal is to explore the concept of multiple primary keys of a table in MySQL. Many-a-times, businesses, and organizations must assign certain columns as the primary key.

This primary key has multiple purposes and reasons to beset its setup. It is essentially used to ensure that each column entry assigned as the primary key is unique.

If more than one column has been assigned as the primary key, then the combination of values across these should be unique. Note that a primary key cannot possess Null values in its column.

We will certainly get an error if we push or insert Null values in this column. Sometimes, it becomes necessary for organizations to get rid of this key to insert multiple similar values or null values.

The table can have more than one primary key. These additional keys are called composite primary keys.

To be precise, multiple primary keys are not assigned to columns, but multiple columns can be described while stating the primary key. Let us understand how this key works and assign multiple columns to the primary key.

Create Table Using student_details_table in MySQL

Before we begin, we create a dummy dataset to work with. Here we create a table, student_details_table, along with a few rows.

-- create the table student_details_table
CREATE TABLE student_details_table(
  stu_id int,
  stu_firstName varchar(255),
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id, stu_firstName)
);
-- insert rows to the table student_details_table
INSERT INTO student_details_table(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Here as we can see, we have set the stu_id and stu_firstName as the primary key for our table student_details. This would ensure that the values in this column cannot be duplicated or NULL.

The above query creates a table with rows with first and last names. To view the entries in the data, we make use of the following code.

SELECT * FROM student_details;

The query mentioned above would give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Use the DESCRIBE Statement Shows the Structure of Table in MySQL

Now let us use the DESCRIBE statement and see if we have more than one column associated with the primary key.

DESCRIBE name_of_the_table;

In our case, we would need to write the following query to understand the details of the table student_details_table.

DESCRIBE student_details_table;

This statement would help us fetch the table’s intricate details like the data type associated with each column, different columns and their names, the keys associated with each column, and any extra information relevant to the table.

Field				Type			Null	Key		Default		Extra
stu_id				int				NO		PRI	
stu_firstName		varchar(255)	NO		PRI	
stu_lastName		varchar(255)	YES			

The table above shows that the fields stu_id and stu_firstName are considered primary keys.

One might need to do this because many a time, businesses need to maintain records such that there exist no duplicate combinations of certain columns.

For example, suppose a product-based company needs to limit customer orders and the number of products associated with the customer every day.

In that case, they might need to set two primary keys as customer id and product id to match the data and perform any operation necessary without any duplicate combination.

Therefore, with the help of the queries above, we can efficiently set multiple primary keys to a table in MySQL.