MySQL database constraints and data table design principles

1. Database Constraints

1.1 Introduction

As the name suggests, constraints in the database are restrictions on the data inserted into the database. The purpose of this is to ensure the validity and integrity of the data. This greatly improves the quality of data in the database, saves database space and the time for calling data.

I have previously introduced the data types in MySQL. The data types can actually verify part of the data. The same goes for constraints, which can also verify some data. This will avoid any illegal data.

1.2 Constraint Types

Common types of constraints are as follows:

• not null : Indicates that a column cannot store null values
• unique : Ensure that each row in a column must have a unique value and cannot have duplicates
• default : Specifies the default value when no value is assigned to the column
• primary key key: represents the primary key, which is a combination of not null and unique . Ensuring that a column (or combination of columns) has a unique identifier helps to find a specific record in the table more easily and quickly
• foreign key : ensures referential integrity that data in one table matches values ​​in another table
• check : Ensures that the values ​​in the column meet the specified conditions. For MySQL database, you can use check, but its use results are ignored

1.3 not null

If not null is used, it means that null can be inserted by default (that is, the value of this column can be left blank)

However, when doing some questionnaires, there are required items. In this case, these options cannot be empty, so you can use not null.

Example:

1.4 unique

If unique is not used, the values ​​of a column in different rows can be repeated.

But in real life, for example, ID card numbers, phone numbers, etc. are actually unique values, so you can use unique

Example:

Notice:

When a column has unique constraint, a search will be performed before inserting. If the value to be inserted does not exist, it will be inserted. Therefore, using unique will actually affect efficiency, but generally speaking, the advantages outweigh the disadvantages.

1.5 default

When we do not use default to specify the default value of a column, the default value is null. If you want to change the default value of a column, you can use default

Example:

1.6 primary key

primary key , which represents the identity of a record, which is equivalent to using not null and unique at the same time.

Example:

Notice:

• A table can have only one primary key.
• The primary key can be formed by combining multiple columns

Supplement: Automatic increment - auto_increment

MySQL can automatically assign the next available number to each row, eliminating the need to manually assign a unique value when adding a row (which can be done manually). But it must be used when create table, for example:

Note: When using a mix of auto-increment and manual allocation, the unique values ​​are not necessarily in order.

1.7 foreign key

A foreign key is foreign key constraint that can be used to associate the primary key or unique value of another table.

For example, if you shop on Taobao, a database can have two data tables: the product table and the order table, and both tables can contain product numbers. The product number in the order table can only appear if it exists in the product table. Therefore, foreign keys can be used to constrain and prevent abnormal data from appearing.

Example:

effect:

A foreign key constraint binds two tables together. The table being constrained is called a child table, and the table that constrains others is called a parent table. If the value to be inserted into the column in the child table does not exist in the associated primary key or unique value in the parent table, the insertion will fail.

Note: Foreign key constraints will prevent records in the parent table from being deleted directly. So what should you do if you want to delete records in the parent table? (For example, if you want to remove a product, the product number will also be gone)

Solution: logical deletion (do not directly delete the database records, do not break the foreign key constraints)

Introduce a new field in the product table to indicate whether the record is valid or invalid. The default value of this field can be set to 1, which means valid. If you want to delete this product, change this field to 0, and the product will be invalid.

1.8 check

check constraint specifies the data that can be entered into a column. For example, gender can only be entered as male or female.

Notice:

MySQL does not support this constraint yet. Even if it is used, the effect of this constraint will be ignored.

Example:

2. Design of database tables

After we have mastered the basic database operation methods, if we are faced with the operation of building a table from scratch, we must have a good grasp of the entities we need and the relationships between them.

There are four main types of relationships between entities:

• No relationship
• One-to-one relationship
• One-to-many relationships
• Many-to-many relationships

2.1 One-to-one relationship

For example, a student corresponds to a user identity of a system, and we can have two design methods:

Put student information and user information into one table

Put students in table A and user information in table B. In table B, you can insert student IDs to associate students with users.

A table:

Table B:

2.2 One-to-many relationship

For example, there is generally a one-to-many relationship between a middle school student and his class. A student can only have one class, and a class can have multiple students.

We can usually design two tables, table A for storing student information and table B for class information, and add classes to the student information to associate

Table A:

Table B:

2.3 Many-to-many relationships

For example, there is a many-to-many relationship between college students and courses. A student may have multiple different courses, and a course may have many students to choose from.

At this time, we design three tables, namely, table A for storing student information, table B for storing course information, and table C for connecting students with courses.

Table A:

Table B:

C Table:

This is the end of this article about the constraints of MySQL database and the design principles of data tables. For more information about the constraints of MySQL database and data table design, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!