In-depth understanding of MySQL composite primary keys

MySQL is a popular relational database management system, and in database design, the primary key is a column or a group of columns in the table, whose value uniquely identifies each row of data. In MySQL, the primary key can be a single-column primary key or a composite primary key. This article will delve into the concept of MySQL composite primary keys and help readers better understand through specific code examples.

What is a composite primary key

A composite primary key is a primary key composed of multiple columns. In this way, a row of data can be uniquely identified more accurately. In a table, there may be situations where the combined value of multiple attributes is unique. In this case, you can consider using a composite primary key to define this relationship. In MySQL, you can define a composite primary key by specifying multiple columns as the primary key when creating the table.

Sample database table

In order to better demonstrate the use of composite primary keys, we create a table named students, which contains students’ student numbers, names, ages, etc. field. In this example, we assume that the combination of student ID and name uniquely identifies each student, so we use student ID and name as the composite primary key.

CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(50),
    age INT,
    PRIMARY KEY (student_id, student_name)
);

In the above example code, we created a table named students through the CREATE TABLE statement, where student_id and student_name The columns are combined into a composite primary key.

Insert data

Next, we insert some data into the students table to demonstrate the use of composite primary keys:

INSERT INTO students (student_id, student_name, age) VALUES
(1, 'Alice', 20),
(2, 'Bob', 21),
(3, 'Alice', 22);

In the above example, we inserted three rows of data into the table, including the same name but different student numbers. In this case, the role of the composite primary key It manifests itself.

Query data

When we want to query specific student information, we can use the composite primary key to accurately locate the data row:

SELECT * FROM students WHERE student_id = 1 AND student_name = 'Alice';

The above query statement will return the student information with student number 1 and name Alice. Due to the existence of the composite primary key, the query operation is more accurate and faster.

Update data

If you need to update a student's information, you can also use a composite primary key to locate a specific data row:

UPDATE students SET age = 23 WHERE student_id = 1 AND student_name = 'Alice';

The above update statement will update the age of the student with student number 1 and name Alice to 23 years old.

Delete data

Using a composite primary key can also easily delete specific data rows:

DELETE FROM students WHERE student_id = 2 AND student_name = 'Bob';

The above delete statement will delete the student information with student number 2 and name Bob.

Summary

Through the above code examples, I believe readers have a deeper understanding of MySQL composite primary keys. Composite primary keys can help us identify data rows more accurately and support fast query, update, and delete operations. In actual database design, it is very important to choose whether to use composite primary keys according to specific business needs. I hope the content of this article will be helpful to readers.

The above is the detailed content of In-depth understanding of MySQL composite primary keys. For more information, please follow other related articles on the PHP Chinese website!