How to set constraints on tables in MySQL database

1. PK (primary key constraint)

1. What is a primary key?

• Before understanding the primary key, first understand what a keyword is

• Keyword: a unique field in the table, such as a A person’s ID number and student number. There can be multiple keywords in a table.

• The primary key is a combination of one or more keywords. Information about the entire table can be obtained through the primary key. It is sometimes also called the primary key. Taking the order table as an example, you can obtain the order's consignee name, product information, price and other related information through the order number.

Note: The keyword does not have to be the primary key, the primary key must be the keyword

Features: The primary key cannot be empty, is unique and cannot be repeated. A table either has only one primary key or no primary key, and cannot have multiple primary keys.

2. How to set the primary key?

Method 1: Set the primary key when creating the table

-- 建立User表
CREATE TABLE User(
User_id int NOT NULL,
User_name VARCHAR(20),
User_pwd VARCHAR(18),
-- 在这里设置id为主键
PRIMARY KEY(User_id)
)

CREATE TABLE Users(
-- 也可以在字段中直接设置主键
User_id int NOT NULL PRIMARY KEY,
User_name VARCHAR(20),
User_pwd VARCHAR(18)
)

Both of the above methods can create a primary key with the same effect.

Method 2: No primary key is set when creating the table, and then set the primary key in the table

• First create a table without setting the primary key

CREATE TABLE User(
User_id int NOT NULL,
User_name VARCHAR(20),
User_pwd VARCHAR(18)
)

• Then set the primary key

The principle is to add PRIMARY KEY (User_id) to the code when creating the table
Follow the method in method one The first principle is the same

ALTER TABLE user ADD PRIMARY KEY(User_id)

The principle is to add PRIMARY KEY to the User_id field
The same as the second principle in method one

ALTER TABLE users MODIFY User_id INT PRIMARY KEY;

2. FK (foreign key constraint)

1. What is a foreign key?

• Foreign keys are also called foreign keywords and represent the direct connection between two tables. The foreign key of one table must be the primary key of another table. The table with the foreign key of another relationship as the primary key is called the master table, and the table with the foreign key is called the slave table of the master table.

• For example, the order table contains two foreign keys: product ID and user ID. The product ID is the primary key of the product information table, and the user ID is the primary key of the user table.

Note: There can be multiple or no foreign keys in a table.

2. How to set foreign keys

Method 1: Set foreign key constraints when creating a table

Let’s take an example based on the previous setting of the primary key. We created a user table earlier. So now create an orders table.

CREATE TABLE dingdan(
DDid INT PRIMARY KEY NOT NULL,
User_id INT NOT null,
DDname VARCHAR(20) NOT NULL,
-- 设置约束关系，dingdan表中的User_id 与 user表中的User_id表示的是同一个数据
constraint fk FOREIGN KEY(User_id) REFERENCES user(User_id)
)

Method 2: The primary key is not set when creating the table, and the primary key is set in the table later

alter table student add constraint stfk foreign key(stid) references teacher(tid)

Note: After the primary and foreign key relationships are established, the data in the main table cannot be deleted at will. If a user's information is included in the order data, an error will occur when deleting the user's information, so the user's information needs to be retained.

3. unique (unique constraint)

1. What is a unique constraint?

If a unique constraint is set on a field, then the field must either not be written, or if written, it cannot be repeated.

2. How to set unique constraints

Still create a user table, this time there is an email field in the table. Setting the email cannot be repeated

CREATE TABLE User(
User_id int NOT NULL,
User_name VARCHAR(20),
User_pwd VARCHAR(18),
User_Email VARCHAR(40) UNIQUE
)

4. not null (not empty)

means that this field cannot be empty

Follow the above unique constraint to write, here it is stipulated that not only the email cannot be empty Repeat and cannot be empty

CREATE TABLE User(
User_id int NOT NULL,
User_name VARCHAR(20),
User_pwd VARCHAR(18),
User_Email VARCHAR(40) UNIQUE not null
)

5. default (default value)

means that if you don’t write this field, you will be given a value by default and continue to write in the User table above. Add a gender field here. If you don’t write gender, the default is male

CREATE TABLE User(
User_id int NOT NULL,
User_name VARCHAR(20),
User_pwd VARCHAR(18),
User_gender enum('男','女') default '男'
)

6. auto_increment (self-increment)

Auto-increment means automatic increment, that is to say, if you don’t have any in this field Fill in the data, and the system will automatically add 1

based on the previous data. Generally used in ID, the number

sets the ID in the user table to auto-increment

CREATE TABLE User(
User_id int auto_increment,
User_name VARCHAR(20),
User_pwd VARCHAR(18),
User_gender enum('男','女') default '男'
)

Note: No. If a piece of data is not filled in, a value will be given by default. So even if the ID is the primary key, you don’t need to fill in the data

The above is the detailed content of How to set constraints on tables in MySQL database. For more information, please follow other related articles on the PHP Chinese website!