SoFunction
Updated on 2025-03-02

Deeply understand primary keys, super keys, candidate keys, and foreign keys in MySQL

In MySQL databases, keys (Keys) are an important concept used to ensure data integrity and consistency. Keys are not only used to uniquely identify records in tables, but also to establish relationships between tables. This article will introduce in detail four key types in MySQL: Primary Key, Super Key, Candidate Key and Foreign Key, and explore their role in database design and management.

Primary Key

A primary key is a combination of one or more fields in a table that uniquely identifies each row record in the table. Primary keys have the following characteristics:

  • Uniqueness: The value of the primary key field must be unique in the table and repetition is not allowed.
  • Non-empty: The value of the primary key field cannot be NULL, each row must have a primary key value.
  • Immutability: Once a record is created, its primary key value cannot usually be changed.

A primary key can consist of one or more fields. If it consists of multiple fields, it is called a composite primary key. The choice of primary key is crucial to the performance of the database and the integrity of the data. For example, in the user table, you can useuser_idAs a primary key, ensure that each user has a unique identifier.

Create primary key

The primary key is used to uniquely identify each row in the table. When creating a table, you can usePRIMARY KEYKeywords to define primary keys.

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

In this example,user_idis defined as a primary key and usedAUTO_INCREMENTattributes so that each time a new record is inserted,user_idThey will automatically increase.

Super Key

A super key is a collection of one or more fields whose values ​​can uniquely identify each row in the table. Super keys contain the characteristics of primary keys, namely uniqueness and non-empty, but they do not require minimization. In other words, the super key may contain redundant properties that are not required for uniqueness. For example, ifuser_idIt's the primary key, then(user_id, username)It's also a super key, becauseusernameFields are not required for uniqueness.

Create a super key

A super key is a combination of one or more fields that uniquely identifies each row in a table. Although superkeys are not defined directly in SQL, they can be achieved by defining primary keys or unique constraints.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (user_id),
    UNIQUE (username, email)
);

In this example,(username, email)is a super key because their combination can uniquely identify each row.

Candidate Key

A candidate key is a subset of the super key, which is the smallest set of super keys, that is, it is no longer a super key after any attribute is removed. The candidate key has uniqueness and non-emptyness, and it is a potential candidate for the primary key. A table may have multiple candidate keys, but only one can be selected as the primary key. For example, in the user table, ifuser_idandemailAll can uniquely identify users, so they are candidate keys. If selecteduser_idAs the primary key, thenemailStill a candidate key, but not a primary key.

Create candidate keys

The candidate key is the smallest set of super keys, that is, it is no longer a super key after any attribute is removed. Candidate keys are usually implemented by defining unique constraints.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (user_id),
    UNIQUE (username),
    UNIQUE (email)
);

In this example,usernameandemailAll candidate keys are because they each uniquely identify each row.

Foreign Key

A foreign key is a key used to establish a relationship between a table. It is one or more fields in a table whose value refers to the primary key of another table. Foreign keys ensure the reference integrity of the data, i.e., the foreign key value in one table must exist in the primary key of another table. For example, if there is an order table and a user table, theuser_idFields can be used as foreign keys to refer to the user tableuser_idPrimary key, ensuring that each order is associated with a valid user.

Create a foreign key

Foreign keys are used to establish relationships between tables. When creating a table, you can useFOREIGN KEYKeywords to define foreign keys.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    user_id INT,
    order_date DATE NOT NULL,
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

In this example,ordersThe tableuser_idThe field is defined as a foreign key, which references theusersThe tableuser_idPrimary key. In this way, each order is associated with a valid user.

Practical application

In actual database design, the concept of these keys is very important. For example, consider a simple library management system with two tables:booksandauthors。booksThere may be onebook_idPrimary key and oneauthor_idForeign keys, andauthorsThere is a tableauthor_idPrimary key. so,booksEvery book in the table can be passedauthor_idForeign keys are associated withauthorsAn author in the table.

In addition, ifbooksThe tabletitleThe fields are unique, then(book_id, title)It can be a super key, andbook_idandtitleAll candidate keys. In this case, selectbook_idBeing a primary key is because it is more stable and will not change due to changes in the title.

in conclusion

In MySQL databases, primary keys, super keys, candidate keys, and foreign keys are key tools for ensuring data integrity and establishing relationships between tables. Primary keys are used to uniquely identify records in tables, super keys are a broader concept that contains primary key characteristics, candidate keys are potential candidates for primary keys, and foreign keys are used to establish relationships between different tables. The correct understanding and application of these key concepts is essential for designing efficient and reliable database systems. By rationally selecting and using these keys, data consistency, integrity and system performance can be ensured.

This is the end of this article about in-depth understanding of primary keys, super keys, candidate keys, and foreign keys in MySQL. For more related contents of MySQL primary keys, super keys, candidate keys, and foreign keys, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!