What does restrict mean in mysql

In mysql, restrict means restriction, which refers to a restriction on the data in the table. It can help the database administrator better manage the database and ensure the correctness and validity of the data in the database. and integrity. MySQL supports 6 types of constraints: primary key constraints, foreign key constraints, unique constraints, check constraints, non-null constraints and default value constraints.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, restrict means restriction.

What are constraints in MySQL?

restrict (constraint) refers to a restriction on the data in the table, which can help the database administrator better manage the database and ensure the correctness, validity and integrity of the data in the database .

For example, when storing age values ​​in a data table, it would be meaningless to store invalid values ​​such as 200 and 300. Therefore, it is necessary to use constraints to limit the range of data in the table.

Constraints in MySQL are used to detect the correctness and rationality of data. Common errors include: 1. The same duplicate data exists; 2. Wrong data; 3. Data loss errors (empty data) ; 4. Refers to unclear errors (tables cannot be accurately linked), etc.

The constraints of MySQL are used to solve the above problems. For example, the ID number cannot be repeated and the delivery address cannot be empty. If a constraint is violated, the data manipulation behavior is suppressed.

In MySQL, the following 6 types of constraints are mainly supported:

1) Primary key constraints

Primary key constraints are the most frequently used constraints. When designing a data table, it is generally required to set a primary key in the table.

The primary key is a special field in the table that uniquely identifies each piece of information in the table. For example, the student number in the student information table is unique.

2) Foreign key constraints

Foreign key constraints are often used together with primary key constraints to ensure data consistency.

For example, a fruit stall has only four kinds of fruits: apples, peaches, plums, and watermelons. Then, when you come to the fruit stall to buy fruits, you can only choose apples, peaches, plums, and watermelons, and you cannot buy other fruits.

3) Unique constraints

Unique constraints have a similarity with primary key constraints, that is, they can both ensure the uniqueness of columns. Different from primary key constraints, there can be multiple unique constraints in a table, and the column where the unique constraint is set is allowed to have null values, although there can only be one null value.

For example, in the user information table, to avoid duplicate user names in the table, you can set the user name column as a unique constraint.

4) Check constraints

Check constraints are a means to check whether the field values ​​in the data table are valid.

For example, the age field in the student information table does not have negative numbers, and the values ​​are also limited. If you are a college student, your age should generally be between 18 and 30 years old. When setting the check constraints of the field, set them according to the actual situation, which can reduce the input of invalid data.

5) Non-null constraint

Non-null constraint is used to constrain the fields in the table that they cannot be empty. For example, in the student information table, if the student name is not added, then this record is useless.

6) Default value constraint

Default value constraint is used to constrain that when no value is entered in a field in the data table, an already set value is automatically added to it.

For example, when registering student information, if the student's gender is not entered, a gender will be set by default or "unknown" will be entered.

Default value constraints are usually used on columns that have set non-null constraints, which can prevent errors when entering data into the data table.

Among the above 6 constraints, there can only be one primary key constraint in a data table, and there can be multiple other constraints.

Constraints of operating tables

1. Add primary key constraints to a table

A table’s constraints There can only be one primary key, and there are two ways to add a primary key.

# Single main key: Use a column as the primary key column. When the value of the column is repeated, it violates the unique constraint.

Union primary key: Use multiple columns as primary key columns. When the values ​​of multiple columns are the same, the unique constraint is violated. It refers to: using a combination of multiple columns to determine the primary key. When a combination is repeated, it will be invalid, such as a combination of 00,01,10,11,12,22.

Use DDL statements to add primary key constraints. Example:

ALTER TABLE 表名 ADD PRIMARY KEY(列名);
alter table emp add primary key(employee_id);//选取employee_id作为主键
alter table emp add primary key(employee_id,shenfen_id);//联合主键

2. Add an auto-increment primary key constraint to a table

auto_increment. The primary key is automatically added when the database management system automatically maintains the table, and the primary key is determined by using natural numbers to increase automatically. Similarly, self-increasing primary keys also use a certain column as the primary key. When data is added to the field where the primary key is added to the table, the primary key is automatically incremented by 1.

Self -growth primary key and general primary keys can be transformed into each other.

1. Only one column in a tablecan be automatically grown. 2. The type of the automatically growing column must be integer type. 3. Automatic growth can only be added to columns with primary key constraints and unique constraints. 4. Delete the primary key constraint or unique constraint. If the column has automatic growth capability, you need to remove the automatic growth first and then delete the constraint.

Note: If you have added an auto-increment attribute to a field before, you must first delete the auto-increment attribute when deleting this field.

alter table 表名 modify 主键 类型 auto_increment;
alter table emp modify employee_id int auto_increment;

Or add a primary key in Navicat:

3. Delete the primary key constraint in a table

        Use the DDL statement to delete the primary key.

ALTER TABLE 表名 DROP PRIMARY KEY;
alter table emp drop primary key;

Note: When deleting the primary key, if the primary key column has the ability to automatically grow, you need to remove the automatic growth first, and then delete the primary key.

alter table emp modify employee_id int;//去掉自增长，与添加自增长代码地区别就是没有auto_increament
alter table emp drop primary key;

4. Add foreign key constraints to a table

        UseDDL statement adds foreign key constraints. Multiple foreign keys can be set for a table.

ALTER  TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN  KEY(列名) REFERENCES 参照的表名(参照的列名);
alter table emp add constraint emp_fk
foreign key(dept_id) references
departments(department_id);

Or add foreign keys in Navicat. Foreign key names are often used: table name _fk, table name _fk1 represents.

5. Delete the foreign key constraints of a table

Use DDL Statement to delete foreign key constraints. Or actively delete foreign keys in Navicat.

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;
alter table emp drop foreign key emp_fk;

6、给表中某字段添加唯一性约束

        使用DDL语句添加唯一性约束。或在在Navicat中添加唯一性约束。

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);
alter table emp add constraint emp_uk unique(name);

7、删除表中某字段的唯一性约束

        使用DDL语句删除唯一性约束。删除之前需要弄清楚删除的约束名是对哪一个字段进行约束的。

ALTER TABLE 表名 DROP KEY 约束名;
alter table emp drop key emp_uk;

8、给表中某个字段添加非空约束

        使用DDL语句添加非空约束。或者Navicat。

ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL;
alter table emp modify salary float(8,2) not NULL;

9、删除表中某个字段的非空约束

        使用DDL语句删除非空约束。

ALTER TABLE 表名 MODIFY 列名 类型 NULL;
alter table emp modify salary float(8,2) NULL;

创建表的时候添加约束

        上面介绍了当一张表已经存在之后如何添加约束。下面介绍在一开始新建表的时候如何添加约束。

1、查询表中加了哪些约束？

 SHOW KEYS FROM 表名;

2、创建表时添加约束

        创建 depts 表包含 department_id 该列为主键且自动增长，department_name 列不允许重复，location_id 列不允含有空值。

create table depts
(
department_id int primary key auto_increment,
department_name varchar(30) unique,
location_id int not null;
);

【相关推荐：mysql视频教程】

The above is the detailed content of What does restrict mean in mysql. For more information, please follow other related articles on the PHP Chinese website!