preface

Integrity constraint

Definition of integrity constraints

To ensure the correctness and validity of the inserted data, add constraints other than data type constraints to the table fields. Summarized a MySQL knowledge graph to share with you.

Classification of integrity constraints

Entity integrity

Records cannot be duplicated.

Primary key constraint

Unique and cannot be null

Unique constraint

The only one that can be empty

Primary key increment (AUTO_increment)

Domain integrity

Fields in a database table that must conform to a specific data type or constraint.

Type constraints

When the table is created, each field is already typed

Not null constraint

not null

The default value

default

Referential integrity

The values of a field in one table need to refer to the values in another table.

Add foreign key constraints

foreign key

Referential integrity can reduce the efficiency of SQL execution, and sometimes not use it at all.

Primary key constraint

Primary key constraint

Unique and cannot be empty;

A table can have only one primary key field, but can have a joint primary key;

There are three ways to add a primary key constraint:

Method 1: Add primary key constraints while creating a table. create table student( sid int primary key, sname varchar(20), age int )charset=utf8; Add primary key constraint, insert same SID, error Insert into sc (sid,sname,age) values (1,' sname ',22); Insert into sc (sid,sname,age) values (1,' sname ',33); Method 2: Add the primary key constraint at the same time as creating the table, but the method is different. Create table student(sid int, sname varchar(20), age int, primary key(sid))charset=utf8; Method 3: After creating a table, add primary key constraints. create table student( sid int, sname varchar(20), age int )charset=utf8; alter table student add primary key(sid); -- constraint indicates that a constraint is added; -- pk_sid gives the constraint a name;Copy the code

The difference between a single primary key and a combined primary key

“Single primary key” sets a field in a table to the primary key, using this field to uniquely identify a record. As long as the SID is unique and not empty, each row is a unique record. Primary Key (SID) SID sName Age 1 triple 18 2 triple 18

“Joint primary key” sets two fields (or fields) in a table as the joint primary key and uses these two fields (or fields) to uniquely identify a record. These two fields can be repeated separately, as long as they are not repeated simultaneously. Primary key(sid, Classid) sid Classid sname age 1 2 zhang3 18 1 3 zhang3 18 2 4 Li4 20 3 4 王 王 5 30 Insert an error. 5 5 Li Chuang 25 5 5 Wang Er 24

Primary key field selection principles

It is common to choose meaningless fields as primary key fields, such as the ID field that records the number of each row in a table, which is meaningless and suitable for primary key fields

Primary key fields are generally not modified (like field name, field type, etc.)

Frequently changing fields, meaningful fields, do not work with primary keys

Note: in a table construction clause where only the primary key is limited and the auto_increament is not used, we need to insert the value of the primary key, otherwise we will get the following error. ERROR 1364 (HY000): Field ‘sid’ doesn’t have a default value”

However, as mentioned above, primary key fields are generally not modified, that is, do not voluntarily give a value, the primary key field should be assigned by default. This should be clear to you when you learn about primary key increment.

Remember: Primary key and auto_increament are best used together.

For example:

Delete the primary key constraint

The meaning and characteristics of primary key increment

As the name suggests, this is a constraint that helps the primary key automatically add values. As mentioned above, it is best to leave the primary key field unchanged. This includes not manually adding values to the primary key field, but letting the system assign values to the primary key field automatically. Primary key auto-increment has the following two characteristics:

1. After the primary key auto-increment is set, the primary key starts from 1 and increases by 1 each time.

2. A previously used primary key. When you delete the row, the primary key is still a new value. An illustration of the second feature:

There are two ways to add a unique constraint:

Method 1: When creating a table, add the primary key increment as well as the primary key increment. create table student( sid int primary key auto_increment, sname varchar(20), age int, idcard varchar(18) )charset=utf8; Alter table auto_increment increment increment increment increment increment increment increment increment increment increment increment increment increment increment Insert into sc (sname,age) values (" sname ", 60),(" sname ", 60); Method 2: After creating a table, add primary keys and increment primary keys to each field. create table student( sid int, sname varchar(20), age int, idcard varchar(18) )charset=utf8; You can add both primary key and primary key increment to a field in either of the following ways. alter table student modify sid int primary key auto_increment; alter table student change sid sid int primary key auto_increment;Copy the code

Delete the primary key autoincrement

alter table student modify sid int primary key;

The only constraints

The meaning of a unique constraint

Since there can only be one primary key in a table. But for some fields, such as id, it must be unique, so we can’t set it as a primary key (id is a meaningful field), so how can we ensure that it is unique? That’s the only constraint I’m going to talk about. Note: A table can have more than one unique constraint.

There are three ways to add a unique constraint:

Method 1: Add a unique constraint while creating a table. create table student( sid int primary key key auto_increment, sname varchar(20), age int, idcard varchar(18) unique )charset=utf8; Method 2: When creating a table, add unique constraints, but in different ways. "Only this way of adding a primary key, Create table student (sid int primary key auto_increment, sname varchar(20), age int, idcard varchar(18), unique(idcard) )charset=utf8; Method 3: After creating a table, add a unique constraint. create table student ( sid int primary key auto_increment, sname varchar(20), age int, idcard varchar(18) )charset=utf8; alter table student add unique(idcard); -- Set a unique constraint on the field, when the same value is inserted, an error is reported. Insert into sc (sname,age,idcard) values (" sname ",18,"123456"); Insert into sc (sname,age,idcard) values (" sname ",22,"123456");Copy the code

Delete unique constraints

Delete unique constraints using a slightly different statement. alter table student drop key idcard;Copy the code

Add a non-null constraint

Add a non-null constraint

A non-null constraint is an error when we insert a null value into a field after setting a non-null constraint on that field. There are two ways to add a non-null constraint:

Method 1: When creating a table, add a non-null constraint to the field. create table student( sid int primary key auto_increment, sname varchar(20) not null, age int, idcard varchar(18) )charset=utf8; Method 2: After the table is created, add non-empty constraints to the fields. create table student( sid int primary key auto_increment, sname varchar(20), age int, idcard varchar(18) )charset=utf8; There are two ways to add it. alter table student modify sname varchar(20) not null; alter table student change sname sname varchar(20) not null;Copy the code

A field with a non-null constraint has the following characteristics

1. You cannot insert null values after a field is set to a non-null constraint.

2. When a field is set to a non-null constraint, not only cannot insert null values, but also cannot insert values.

"These are details that sometimes we do not notice, so we separate them out." You cannot insert null values after a field is set to a non-null constraint. The following insertion will cause an error. insert into student(sname,age) values (null,22); When a field is set to a non-null constraint, not only cannot insert null values, but also cannot insert values. The following inserts will also return an error. insert into student(age,idcard) values (33,"123456789");Copy the code

Examples are as follows:

Add default value constraints

When a field is set to a default value, it is displayed when we do not specify a value for the field.

Add default value constraints

Method 1: When creating a table, add default values for the fields. create table student( sid int primary key auto_increment, sname varchar(20), age int default 0, idcard varchar(18) )charset=utf8; Method 2: After the table is created, add default values for the fields. create table student( sid int primary key auto_increment, sname varchar(20), age int, idcard varchar(18) )charset=utf8; "Different, need special attention" -- the code that uses ALTER to add default values is a bit special. alter table student alter age set default 0;Copy the code

Delete default Values

alter table student alter column age drop default;

Referential integrity

What is referential integrity

Referential integrity means that the value of a field in one table must refer to the value of a field in another table. This is a foreign key. Generally, the field in this table is a foreign key. You need to set it to the primary key of the table based on the value of the field in the table.

Refer to the following figure to help understand:

Note that the CID in the student table refers to the CID in the class table, and as you can see, the field names are exactly the same. However, in practice, the two field names can be different, but the data type of the two fields must be the same.

Conditions that constitute referential integrity:

1. A field in one table (reference table) refers to a field in another table (reference table) (the field name may be different).

2. The field names in the two tables can be different, but the data types of the fields must be the same.

3. Refer to the fields in the table as foreign keys. The field in the referenced table is the primary key in that table.

Add foreign key constraints

One thing to remember is that you can add a foreign key constraint to a table before it has its own referenced table. Otherwise, who do you refer to?

You can add a foreign key constraint in either of the following ways:

Method 1: When creating a table, add a foreign key constraint to the field. create table classroom( cid int primary key auto_increment, cname varchar(20) not null )charset=utf8; create table student( sid int primary key auto_increment, sname varchar(20) not null, age bit(1), cid int, constraint fk_cid foreign key(cid) references classroom(cid) )charset=utf8; Method 2: After the table is created, add foreign key constraints to the fields. create table classroom( cid int primary key, cname varchar(20) not null )charset=utf8; create table student( sid int primary key auto_increment, sname varchar(20) not null, age bit(1), cid int )charset=utf8; alter table student add constraint fk_cid foreign key(cid) references classroom(cid); "Note: constraint is followed by an alias, and fk_cid is equivalent to the alias given."Copy the code

The last

I here collated a MySQL database information document Spring series family, Java systematic information (including Java core knowledge points, interview topics and 20 years of the latest Internet real questions, e-books, etc.) friends who need to pay attention to the public number [procedure Yuan small wan] can be obtained.