JIYIK CN >

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

This primarykey has multiple uses and reasons to bother with its setting. It is essentially used to ensure that each column entry assigned as a primary key is unique.

If multiple columns have been specified as 主keys, the combination of these values ​​should be unique. Note that primarya key cannot have values ​​in its columns Null.

If we push or insert a value in this column Null, we will definitely get an error. Sometimes, it is necessary for organizations to get rid of this key to insert multiple similar values ​​or nullvalues.

The table can have multiple 主keys. These additional keys are called 复合主键.

To be precise, multiple primary keys are not assigned to columns, but primarymultiple columns can be described while declaring the key. Let's understand how this key works and assign multiple columns to primarythe key.

student_details_tableCreate a table in MySQL using

Before we get started, let's create a dummy dataset to work with. Here, we create a table student_details_tableand 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");

As we can see, we have set stu_idand stu_firstNameas the primary key of our table student_details. This will ensure that there cannot be duplicate values ​​or in this column NULL.

The above query creates a table with rows containing first name and last name. To see the entries in the data, we use the following code.

SELECT * FROM student_details;

The above mentioned query will 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 DESCRIBEthe statement to display the structure of the table in MySQL

Now let's use DESCRIBEthe statement to see if we have more than one column associated with the primary key.

DESCRIBE name_of_the_table;

In our case, we need to write the following query to know student_details_tablethe details of the table.

DESCRIBE student_details_table;

This statement will help us get the complex details of the table such as the data type associated with each column, the different columns and their names, the key associated with each column, and any extra information related 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_idand stu_firstNameare considered primary keys.

This may be required because many times, businesses need to maintain records so that there are no duplicate combinations of certain columns.

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

In this case, they may need to set two primary keys as Customer ID and Product ID to match the data and perform any necessary operations without any duplicate combinations.

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