What is the meaning of restrict in mysql

In mysql, restrict means constraint, which refers to a restriction on the data in the table. It can help the database administrator better manage the database and ensure that the data in the database correctness, validity and completeness. In MySQL, there are six types of constraints available, including primary key constraints, foreign key constraints, unique constraints, check constraints, non-null constraints and default value constraints.

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 .

If invalid values, such as 200, 300, are stored in the data table as the age value, then these values ​​​​are meaningless. 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.

MySQL's constraints are designed to solve some problems, such as prohibiting duplicate ID numbers and forcing the delivery address to be non-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 and primary key constraints have something in common, that is, they both guarantee the uniqueness of the column. 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.

The age field in the student information table does not contain negative numbers and has numerical restrictions. If you are a college student, your age should generally be between 18 and 30 years old. In order to reduce the input of invalid data, when setting the check constraints of the field, it is necessary to make appropriate settings according to the actual situation.

5) Non-null constraint

Non-null constraint is used to constrain the fields in the table that they cannot be empty. If the student's name is not filled in the student information form, then this record is meaningless.

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.

Operation table constraints

1. Add primary key constraints to a table

A table can only have one primary key. There are two ways to add a primary key.

Single primary key: Use one column as the primary key column. When the values ​​of this column are repeated, the unique constraint is violated.

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 auto-increment primary key constraint

auto_increment to a table. When the database management system automatically maintains the table, it will automatically add the primary key according to the natural number increment method. 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.

Auto-increasing primary keys and general primary key constraints can be converted into each other.

1, Only one column in a table can automatically grow . 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.

       注意点：如果之前给某个字段添加了自增长属性，则删除这个字段时要先删除自增长属性。

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

或者在Navicat中添加主键：

3、删除一张表中的主键约束

使用DDL语句删除主键。

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

       注意：删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键。

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

4、给一张表添加外键约束

使用DDL语句添加外键约束。可以给一张表设置多个外键。

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

或在在Navicat中添加外键。外键名称常使用：表名_fk，表名_fk1表示。

5、删除一张表的外键约束

外键约束可以通过使用DDL语句来删除。或者在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;
);

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