Create constraints for Mysql constraints saved in the table_constraints of information_schema database. You can use this table to query constraints. Constraints mainly test the data and ensure the integrity of the database data; if there is mutually dependent data, ensure that the data is not deleted. Common five types of constraints: notnull: non-null constraint, which indicates
Create constraints for Mysql constraints saved in the table_constraints of information_schema database. You can use this table to query constraints. Constraints mainly test the data and ensure the integrity of the database data; if there is mutually dependent data, ensure that the data is not deleted. Common five types of constraints: not null: non-empty constraint, which indicates
Create constraints for Mysql
MySQL constraints are saved in table_constraints of information_schema database. You can use this table to query constraints;
Constraints mainly test the data to ensure the integrity of the database data. If there is mutually dependent data, ensure that the data is not deleted.
Five common constraints:
Not null: non-null constraint. The specified column is not empty.
Unique: unique constraint. Data of a specified column and several columns cannot be duplicated.
Primary key: primary key constraint. Data in a specified Column cannot be duplicate or unique.
Foreign key: specifies that the column record belongs to one record in the master table.
Check: check, specifying an expression to test specified data
Note: MySQL does not support check constraints, but can use check constraints without any effect;
Constraints can be divided:
Single Column constraint: each constraint only limits one column.
Multi-column constraints: each constraint restricts multiple columns of data.
1. not null
Non-null constraints are used to ensure that the value of the current column is not null. non-null constraints can only appear in columns of table objects.
Null feature: All types of values can be null, including int, float, and other data types. "" is not equal to null, and "0" is not equal to null.
Create table temp (
Id int not null,
Name varchar (255) not null default 'abc ',
Sex char null
)
The preceding table has a non-null constraint. You can also use alter to modify or add a non-null constraint.
Add non-empty Constraint
Alter table temp modify sex varchar (2) not null;
Cancel non-empty Constraint
Alter table temp modify sex varchar (2) null;
Remove non-empty constraints and add default values
Alter table temp modify sex varchar (2) default 'abc ';
2. unique
The unique constraint is that the column or column combination of the specified table cannot be repeated to ensure data uniqueness. Although the unique constraint does not allow repeated values, multiple null values are allowed. The same table can have multiple unique constraints and multiple column combinations. When creating a unique constraint, if the unique constraint name is not given, it is the same as the column name by default. MySQL creates a unique index for the columns with unique constraints by default;
Create table temp (
Id int not null,
Name varchar (25 ),
Password varchar (16 ),
Constraint uk_name_pwd unique (name, password)
);
Indicates that the user name and password combination cannot be repeated.
Add unique constraint
Alter table temp add unique (name, password );
Modify uniqueness constraints
Alter table temp modify name varchar (25) unique;
Delete Constraints
Alter table temp drop index name;
3. primary key
A primary key constraint is equivalent to a combination of unique and non-empty constraints. Duplicate primary key constraint columns and null values are not allowed, therefore, these columns cannot be null and the combined values cannot be repeated. Each table can have only one primary key. You can create a primary key constraint at the column level or at the table level.
The PRIMARY key name of MySQL is always PRIMARY. When a PRIMARY key constraint is created, the system creates a unique index on the column and column combination.
Column mode:
Create table temp (
Id int primary key,
Name varchar (25)
);
Create table temp2 (
Id int not null,
Name varchar (25 ),
Pwd varchar (15 ),
Constraint pk_temp_id primary key (id)
);
Combination Mode:
Create table temp2 (
Id int not null,
Name varchar (25 ),
Pwd varchar (15 ),
Constraint pk_temp_id primary key (name, pwd)
);
Alter Delete primary key constraints
Alter table temp drop primary key;
Alter add primary key
Alter table temp add primary key (name, pwd );
Alter column primary key
Alter table temp modify id int primary key;
Set primary key auto-Increment
Create table temp (
Id int auto_increment primary key,
Name varchar (20 ),
Pwd varchar (16)
);
Auto_increment: in auto-increment mode, you do not need to insert a value for this column when inserting data after auto-increment is set.
4. foreign key
The foreign key constraint ensures the integrity of the reference between one or two tables. The foreign key is built on the two fields of a table or the reference relationship between the two fields of two tables. That is to say, the foreign key value of the table must be found in the master table or be empty. When a record of the primary table is referenced from the table, the record of the primary table cannot be deleted. To delete data, you must first Delete the data dependent on the record from the table, then you can delete the data in the master table. Another way is to cascade the deletion of sub-table data.
Note: For reference columns with foreign key constraints, only primary keys or columns with unique key constraints can be referenced in the primary table. If the referenced primary table column is not a unique record, the data referenced from the table is not sure about the record position. The same table can have multiple foreign key constraints.
Create a foreign key constraint:
Master table
Create table classes (
Id int auto_increment primary key,
Name varchar (20)
);
Slave table
Create table student (
Id int auto_increment,
Name varchar (22 ),
Constraint pk_id primary key (id ),
Classes_id int references classes (id)
);
Generally, the primary table is created first and then the slave table is created, so that the referenced table of the slave table exists.
Table-level foreign key creation constraints:
Create table student (
Id int auto_increment primary key,
Name varchar (25 ),
Classes_id int,
Foreign key (classes_id) references classes (id)
);
No constraint name is specified for the method used to create a foreign key. By default, the system assigns a foreign key constraint name to the foreign key constraint, named student_ibfk_n. student is the table name, n is the integer starting from 1 for the current constraint.
Specify the constraint name:
Create table student (
Id int auto_increment primary key,
Name varchar (25 ),
Classes_id int,
Constraint fk_classes_id foreign key (classes_id) references classes (id)
);
The table-level constraint syntax must be used for the combination of multiple foreign keys:
Create table classes (
Id int,
Name varchar (20 ),
Number int,
Primary key (name, number)
);
Create table student (
Id int auto_increment primary key,
Name varchar (20 ),
Classes_name varchar (20 ),
Classes_number int,
Foreign key (classes_name, classes_number) references classes (name, number)
);
Delete foreign key constraints:
Alter table student drop foreign key student_ibfk_1;
Alter table student drop foreign key fk_student_id;
Add foreign key constraints:
Alter table student add foreign key (classes_name, classes_number) references classes (name, number );
Self-reference and self-Association (recursive table and tree table)
Create table tree (
Id int auto_increment primary key,
Name varchar (50 ),
Parent_id int,
Foreign key (parent_id) references tree (id)
);
Cascading deletion: When you delete data from a primary table, the associated data is also deleted from the table. You need to add on delete cascade or on delete set null after creating a foreign key constraint, the former is cascading deletion, and the latter is to set the value of the joined column from the table to null.
Create table student (
Id int auto_increment primary key,
Name varchar (20 ),
Classes_name varchar (20 ),
Classes_number int,/* Table-level joint foreign key */
Foreign key (classes_name, classes_number) references classes (name, number) on delete cascade
);
5. check
MySQL can use the check constraint, but the check constraint does not have any effect on data verification.
Create table temp (
Id int auto_increment,
Name varchar (20 ),
Age int,
Primary key (id ),
Check (age> 20)
);
The preceding check constraint requires that the age must be greater than 20, but does not work. However, there are no errors or warnings when creating a table.
6. When using MYsql, errors such as ERROR 1005 (HY000): Can't create table may easily occur when the operation is not performed.
There is a group of [latest foreign key error] in which detailed descriptions of recent errors and solutions are provided.
Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.
The Index containing the "referenced field" cannot be found in the "referenced table", or the two associated field types do not match)
The following are two examples:
Example 1:
Create table booktype
(
Btid int (5) unsigned zerofill auto_increment not null primary key,
Btname varchar (100) not null unique,
Btnote text
);
Create table books
(
Bid int (5) unsigned zerofill auto_increment not null primary key,
Bname char (30) not null,
Isbn char (50) not null,
Author char (30) not null,
Press text,
Summary text,
Bcount int not null default 0,
Btid int,
Foreign key (btid) references booktype (btid)
);
Error:
ERROR 1005 (HY000): Can't create table '. \ bookdata \ books. frm' (errno: 150)
The main problems and solutions are as follows:
In foreign key (btid) references booktype (btid), The btid in the books table does not match the type of the associated field set in the int and booktype tables. The btid in the books table is becoming: btid int (5) unsigned zerofill will not report errors. This is often forgotten when creating and modifying tables.
