The definition and function of MySQL composite primary key

The composite primary key in MySQL refers to the primary key composed of multiple fields in the table, which is used to uniquely identify each record. Unlike a single primary key, a composite primary key is formed by combining the values ​​of multiple fields. When creating a table, you can define a composite primary key by specifying multiple fields as primary keys.

In order to demonstrate the definition and function of the composite primary key, we first create a table named users, which contains id, username and email Of these three fields, id is the auto-incremented primary key, username is the user name, and email is the user's email address. We will combine the two fields username and email as a composite primary key.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    PRIMARY KEY(username, email)
);

In the above code, a composite primary key is defined by using PRIMARY KEY(username, email) in the CREATE TABLE statement, and username The two fields and email are combined as a unique identifier.

Next we insert some data into the table. Note that due to the existence of the composite primary key, the combination of username and email of each record must be unique.

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');

If we try to insert a username and email The same data as previously recorded will trigger a unique constraint error:

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
-- Output error: Duplicate entry 'alice@example.com' for key 'PRIMARY'

This is one of the functions of the composite primary key. It ensures that the combined field value of each record in the table is unique to avoid Data is duplicated.

In practical applications, composite primary keys can more accurately control data integrity in data table design. When records in a database need to be uniquely identified through multiple fields, a composite primary key can provide better data integrity protection. At the same time, in some queries and table association operations, composite primary keys also have their special uses, which can help improve query efficiency and accuracy.

To summarize, the composite primary key in MySQL is a primary key composed of multiple fields and is used to uniquely identify each record. This example shows how to define a composite primary key and its role in data integrity and query efficiency. I hope it will be helpful to understand composite primary keys.

The above is the detailed content of The definition and function of MySQL composite primary key. For more information, please follow other related articles on the PHP Chinese website!