Detailed explanation of primary keys in mysql database

Introduction to primary keys

As stated, primary key values ​​must be unique. That is, each row in the table must have a unique primary key value. If a single column is used for the primary key, its values ​​must be unique. If multiple columns are used, the combined values ​​of these columns must be unique.

The role of mysql primary key

The column (or this group of columns) that uniquely identifies each row in the table is called the primary key. Without a primary key, updating or deleting specific rows in a table is difficult because there is no safe way to ensure that only relevant rows are designed.

The CREATE TABLE examples we have seen so far all use a single column as the primary key. The primary key is defined with a statement similar to the following:

PRIMARY KEY (vend_id )

To create a primary key consisting of multiple columns, each column name should be given in a comma-separated list, as follows:

create table orderitems
(
order_num              int              NOT NULL,
order_item             int               NOT NULL,
prod_id              char(10)            NOT NULL,
quantity              int               NOT NULL,
item_price              decimal(8,2)                  NOT NULL,
PRIMARY KEY (order_num,order_item)
)ENGINE = InnoDB;

The orderitems table contains the details of each order in the orders table. Each order has multiple items, but each order only has 1 first item, 1 second item, and so on at any time. Therefore, the combination of order number (order_num column) and order item (order_item column) is unique and thus suitable as a primary key, which is defined as:

PRIMARY KEY ( order_num,order_item)

The primary key can be defined when creating the table (as shown here) , or defined after the table is created.

Primary Key and NULL Value A primary key is a column whose value uniquely identifies each row in the table. Only columns that do not allow NULL values ​​can be used in the primary key. Columns that allow NULL values ​​cannot be used as unique identifiers.

About the primary key auto-increment, we will explain it in detail in the detailed explanation of using AUTO_INCREMENT in mysql to create a data table.

[Related recommendations]

1. php Chinese website special recommendation:php programmer toolbox download ( Build a php environment with one click)

2. mysql self-increasing AUTO_INCREMENT instance usage
   

The above is the detailed content of Detailed explanation of primary keys in mysql database. For more information, please follow other related articles on the PHP Chinese website!