Shulou(Shulou.com)06/01 Report--

Today, I would like to share with you the relevant knowledge points about how to set constraints on the table in the MySQL database. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look.

Competition (primary key constraint) 1. What is a primary key?

Before you know the primary key, let's know what the keyword is.

Keywords: fields that are unique in a table, such as a person's ID number and student number. There can be multiple keywords in a table.

The primary key, also known as the primary keyword, is made up of one or more keywords, and information about the entire table can be obtained through the primary key. For example, the order table, through the order number can get the name, product, price and other information in the order.

Note: the keyword is not necessarily the primary key, the primary key must be the keyword

Features: the primary key can not be empty, unique can not be repeated. A table has either one primary key or no primary key, and cannot have more than one primary key.

2. How to set the primary key?

Method 1: set the primary key when creating the table

-- create the User table CREATE TABLE User (User_id int NOT NULL,User_name VARCHAR (20), User_pwd VARCHAR (18),-- set id as the primary key PRIMARY KEY (User_id)) CREATE TABLE Users (--you can also set the primary key User_id int NOT NULL PRIMARY KEY,User_name VARCHAR (20), User_pwd VARCHAR (18) directly in the field)

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 the primary key is set in the table later.

First create a table with no primary key set

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 the table is created

The same as the first principle in method one.

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; II. FK (Foreign key constraint) 1. What is a foreign key?

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

For example, the order table contains two foreign keys, ID for goods and ID for users. The commodity ID is the primary key of the commodity information table, and the user ID is the primary key of the user table.

Note: there may or may not be multiple foreign keys in a table.

2. How to set foreign keys

Method 1: set foreign key constraints when creating a table

For example, based on the previous setting of the primary key, you created a user table, so now create an order table.

CREATE TABLE dingdan (DDid INT PRIMARY KEY NOT NULL,User_id INT NOT null,DDname VARCHAR (20) NOT NULL,-- sets constraint relationship, User_id in dingdan table and User_id in user table represent the same data constraint fk FOREIGN KEY (User_id) REFERENCES user (User_id))

Method 2: no primary key is set when creating the table, and then 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 foreign key relationship is established, the data in the main table cannot be deleted at will. For example, if there is a piece of data in the order that contains a user, then the user's information cannot be deleted in the user table, otherwise an error will be reported.

3. Unique (unique constraint) 1. What is the unique constraint?

If a field has a unique constraint set, then the field is either not written, and cannot be repeated if you want to write.

2. How to set unique constraints

Or create a user table, this time with mailbox fields. Setting mailbox cannot be repeated.

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

It means that this field cannot be empty

Then write the only constraint above, which stipulates that the mailbox not only cannot be repeated but also 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) V, default (default)

It means that if you don't write this field, it will give you a value by default to continue to write in the User table above, and add a gender field here. If you don't write gender, it will default to male.

CREATE TABLE User (User_id int NOT NULL,User_name VARCHAR (20), User_pwd VARCHAR (18), User_gender enum ('male', 'female') default 'male') VI, auto_increment (self-increasing)

Self-increment means automatic increment, that is, if you do not fill in the data in this field, the system will automatically add 1 according to the previous data.

Commonly used in ID, numbering

Set the ID in the user table to self-increment

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

Note: if the first data is not filled in, it will be given a value by default. So even if ID is the primary key, you can leave the data unfilled.

These are all the contents of the article "how to set constraints on tables in MySQL Database". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.