MySQL constraints

MySQL database (MySQL installation/Basic/advanced/optimized)

1. Overview of Constraints

1.1 Why are Constraints needed

Data Integrity refers to the Accuracy and Reliability of Data. It is proposed to prevent the existence of non-semantic data in the database and to prevent invalid operation or error information caused by the input and output of error information.

To ensure data integrity, the SQL specification imposes additional conditions on table data in the form of constraints. Consider from the following four aspects:

  • Entity IntegrityFor example, two identical and indistinguishable records cannot exist in the same table
  • Domain Integrity: For example: Age range 0-120, gender range male/female
  • Referential IntegrityFor example, the department of an employee must be found in the department table
  • User-defined IntegrityFor example, the user name must be unique and the password must be unique. The salary of the manager must not be higher than five times the average salary of the employees in the department.

1.2 What are constraints

A constraint is a table level constraint that restricts the fields in a table to ensure the accuracy and reliability of the data in the table

Constraints can be specified (added) when the TABLE is created (through the CREATE TABLE statement) or specified (added or removed) after the TABLE is created by the ALTER TABLE statement.

1.3 Classification of constraints

  • Based on the constraints on the data column,Constraints can be divided into:
    • Single-column constraint: Each constraint constrains only one column (field)
    • Multi-column constraint: Each constraint can constrain multiple columns of data
  • Depending on the scope of the constraint, constraints can be divided into:
    • Column-level constraints: Apply to only one column, following the column definition
    • Table-level constraints: Can be applied to multiple columns, not together with the columns, but defined separately
Table level constraint: all columns below default and non-empty are not supported, other support is possible (primary key has no effect).Copy the code
CREATE TABLETable name (Field name field type column level constraint, field name field type, table level constraint)Copy the code
  • According to the role of constraints, constraints can be divided into:
    • NOT NULL Non-null constraint that states that a field cannot be null
    • UNIQUE constraint that specifies that a field is UNIQUE in the entire table
    • PRIMARY KEY Specifies the PRIMARY KEY (non-empty and unique) constraint
    • FOREIGN KEY Foreign key constraints
    • CHECK The check constraint
    • DEFAULT Default constraints

Note: MySQL does not support the check constraint, but you can use the check constraint with no effect

  • View existing constraints on a table
#information_schema Database name (system library)
#table_constraints Table name (dedicated to storing constraints for individual tables)
SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'Table name';
Copy the code

2. Non-null constraints

2.1 role

Specifies that the value of a field/column cannot be null

2.2 the keyword

NOT NULL

2.3 the characteristics of

  • By default, all types can be NULL, including INT, FLOAT, etc

  • A non-null constraint can only occur on a column of a table object. A column can only qualify non-null individually, not in combination

  • A table can have many columns that are individually qualified to be non-null

  • The empty string “” does not equal NULL, and 0 does not equal NULL

2.4 Adding a non-null constraint

(1) When building a table

CREATE TABLETable name (field name data type, field name data typeNOT NULL, field name data typeNOT NULL
);
Copy the code

For example:

CREATE TABLE emp(
	id INT(10) NOT NULL.NAME VARCHAR(20) NOT NULL,
	sex CHAR NULL
);
Copy the code
CREATE TABLE student(
    sid int,
    sname varchar(20) not null,
    tel char(11) ,
    cardid char(18) not null
);
Copy the code
insert into student values(1.'Joe'.'13710011002'.'110222198912032545'); # success
insert into student values(2.'bill'.'13710011002'.null);# ID number is empty
ERROR 1048 (23000): Column 'cardid' cannot be null
insert into student values(2.'bill'.null.'110222198912032546');Tel allowed to be null
insert into student values(3.null.null.'110222198912032547');# failure
ERROR 1048 (23000): Column 'sname' cannot be null

dsec test1
Copy the code

After you create a constraint, you can’t change some fields

update student set sname = null where sid = 1
Copy the code

(2) After the table is built

The add constraint ensures that the attributes of the field to be modified are not set to Null

alter tableThe name of the tablemodifyField name Data typenot null;
Copy the code

For example:

ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL;
Copy the code
alter table student modify sname varchar(20) not null;
Copy the code

2.5 Deleting a Non-empty Constraint

alter tableThe name of the tablemodifyField name Data typeNULL;
# Remove not NULL, which is equivalent to modifying a non-annotated field that is allowed to be null

或 

alter tableThe name of the tablemodifyField name data type;# Remove not NULL, which is equivalent to modifying a non-annotated field that is allowed to be null
Copy the code

For example:

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;
Copy the code
ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;
Copy the code

3. Uniqueness constraint

3.1 role

Used to restrict the value of a field/column from being repeated.

3.2 the keyword

UNIQUE

3.3 the characteristics of

  • The same table can have multiple unique constraints.
  • A unique constraint can be a unique value for a single column or a combination of columns.
  • The uniqueness constraint allows column values to be null and allows multiple records to have null values
  • When creating a unique constraint, the default is the same as the column name if the constraint is not named.
  • MySQL creates a unique index for columns with unique constraints by default.

3.4 Adding a Unique Constraint

(1) When building a table

create tableTable name (field name data type, field name data typeunique, field name data typeunique key, field name data type);create tableTable name (field name data type, field name data type, field name data type, [constraintThe constraint name]unique key(field name));Copy the code

For example:

create table student(
    sid int,
    sname varchar(20),
    tel char(11) unique,
    cardid char(18) unique key
);
Copy the code
CREATE TABLE t_course(
	cid INT UNIQUE,
	cname VARCHAR(100) ,
	description VARCHAR(200)
    Use table level constraint syntax
 	CONSTRAINT uk_name_pwd UNIQUE(cname)Add a unique constraint to cname
);

Copy the code
CREATE TABLE USER(
    id INT NOT NULL.NAME VARCHAR(25),
    PASSWORD VARCHAR(16),
    Use table level constraint syntax
    CONSTRAINT uk_name_pwd UNIQUE(NAME.PASSWORD)Add a unique constraint to the name and password combinations, indicating that the username and password combinations cannot be repeated
);
Copy the code

The user name and password must be unique

insert into student values(1.'Joe'.'13710011002'.'101223199012015623');
insert into student values(2.'bill'.'13710011003'.'101223199012015624');
Copy the code
mysql> select * from student;
+-----+-------+-------------+--------------------+
| sid | sname | tel         | cardid             |
+-----+-------+-------------+--------------------+| 1 | zhang SAN | 13710011002 | | 101223199012015623 | | 13710011003 | | 101223199012015624 + 2 |, dick, and harry-----+-------+-------------+--------------------+
2 rows in set (0.00 sec)
Copy the code
insert into student values(3.'Cathy'.'13710011004'.'101223199012015624'); Duplicate ID number
ERROR 1062 (23000): Duplicate entry '101223199012015624' for key 'cardid'

insert into student values(3.'Cathy'.'13710011003'.'101223199012015625'); 
ERROR 1062 (23000): Duplicate entry '13710011003' for key 'tel'
Copy the code

(2) specify the unique key constraint after the table is created

# If there is a single field in the column list, it means that the value of the column is unique. If there are two or more fields, then compound uniqueness, that is, a combination of multiple fields, is unique
# Method 1:
alter tableThe name of the tableadd unique key(Field list);Copy the code
# Method 2:
alter tableThe name of the tablemodifyField Name Field typeunique;
Copy the code

For example:

ALTER TABLE USER 
ADD UNIQUE(NAME.PASSWORD);
Copy the code
ALTER TABLE USER 
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME.PASSWORD);
Copy the code
ALTER TABLE USER 
MODIFY NAME VARCHAR(20) UNIQUE;
Copy the code

For example:

create table student(
    sid int primary key,
    sname varchar(20),
    tel char(11) ,
    cardid char(18));Copy the code
alter table student add unique key(tel);
alter table student add unique key(cardid);
Copy the code

3.5 On compound Unique constraints

create tableTable name (field name data type, field name data type, field name data type,unique key(Field list)Multiple field names are separated by commas, indicating that a combination of multiple fields is unique
);
Copy the code
# students table
create table student(
    sid int.# student id
    sname varchar(20),			# the name
    tel char(11) unique key.# phone
    cardid char(18) unique key # ID number
);

# the curriculum
create table course(
    cid int.# Course Number
    cname varchar(20)     # Course Name
);

# course selection table
create table student_course(
    id int.sid int,
    cid int,
    score int.unique key(sid,cid)  # compound unique
);
Copy the code
insert into student values(1.'Joe'.'13710011002'.'101223199012015623');# success
insert into student values(2.'bill'.'13710011003'.'101223199012015624');# success
insert into course values(1001.'Java'), (1002.'MySQL');# success
Copy the code
mysql> select * from student;
+-----+-------+-------------+--------------------+
| sid | sname | tel         | cardid             |
+-----+-------+-------------+--------------------+| 1 | zhang SAN | 13710011002 | | 101223199012015623 | | 13710011003 | | 101223199012015624 + 2 |, dick, and harry-----+-------+-------------+--------------------+
2 rows in set (0.00 sec)

mysql> select * from course;
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | Java  |
| 1002 | MySQL |
+------+-------+
2 rows in set (0.00 sec)
Copy the code
insert into student_course values
(1.1.1001.89),
(2.1.1002.90),
(3.2.1001.88),
(4.2.1002.56);# success
Copy the code
mysql> select * from student_course;
+----+------+------+-------+
| id | sid  | cid  | score |
+----+------+------+-------+| | 1 | 1001 | 89 | | 2 | 1 | 1002 | 90 | | 2 | 3 | 1001 | 88 | | | | 1002 | | 56 2 + 4----+------+------+-------+
4 rows in set (0.00 sec)
Copy the code
insert into student_course values (5.1.1001.88);# failure

#ERROR 1062 (23000): Duplicate entry '1-1001' for key 'sid' is in violation of sid- CID compound uniqueness
Copy the code

3.5 Deleting a Unique Constraint

  • Unique indexes are also automatically created on columns that add unique constraints.
  • Delete unique constraints can only be deleted by deleting unique indexes.
  • You need to specify a unique index name, which is the same as a unique constraint name.
  • If a unique constraint is created without a name, it defaults to the same column name if it is a single column. If it is a combined column, the default is the same as the first column in (). You can also customize the unique constraint name.
SELECT * FROM information_schema.table_constraints WHERE table_name = 'the name of the table'; # check what constraints are available
Copy the code
ALTER TABLE USER 
DROP INDEX uk_name_pwd;
Copy the code

Note: you can run the show index from table name; View the index of the table

4. PRIMARY KEY constraint

4.1 role

Uniquely identifies a row of records in a table.

Primary key and unique comparison:

Guaranteed uniqueness Whether to allow null How many can be in a table Whether to allow composition
A primary key Square root x One at most √, but not recommended
The only Square root Square root You can have more than one √, but not recommended

4.2 the keyword

primary key

4.3 the characteristics of

  • A primary key constraint is equivalent to a combination of a unique constraint and a non-null constraint. The primary key constraint column is not allowed to duplicate, nor is a null value allowed.

  • A table can have at most one primary key constraint, and primary key constraints can be created at either the column level or table level.

  • Primary key constraints correspond to one or more columns in a table (compound primary keys)

  • In the case of a composite primary key constraint with multiple columns, none of these columns is allowed to be null, and the combined values are not allowed to be repeated.

  • MySQL > select * from PRIMARY key where constraint name = PRIMARY;

  • When a primary key constraint is created, the system creates a primary key index for the column or column combination by default. (If you can query data based on the primary key, you can query data based on the primary key for higher efficiency.) If the primary key constraint is deleted, the index corresponding to the primary key constraint is automatically deleted. Mysql’s underlying data store uses a B+ tree structure, which is created based on the primary key index. The search efficiency is high. If other indexes are used, it will involve back table.

  • One thing to be careful about is that you do not change the value of the primary key field. Since the primary key is the unique identification of the data record, changing the value of the primary key can compromise data integrity.

4.4 Adding primary Key Constraints

SQL > alter TABLE create primary key constraint

create tableTable name (field name Data type PRIMARYkey.# column level modeField name data type, field name data type);create tableTable name (field name data type, field name data type, field name data type, [constraintConstraint name] primarykey(Field name)Table level schema, constraint name is not used
);
Copy the code

For example:

create table temp(
	id int primary key.name varchar(20));Copy the code
mysql> desc temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Copy the code
insert into temp values(1.'Joe');# success
insert into temp values(2.'bill');# success
Copy the code
mysql> select * from temp;
+----+------+
| id | name |
+----+------+| | 1 zhang SAN | | | |, dick, and harry + 2----+------+
2 rows in set (0.00 sec)
Copy the code
insert into temp values(1.'Joe');# failureERROR 1062 (23000): Duplicate entry (type, type) '1' for key 'PRIMARY'insert into temp values(1.'Cathy');# failure
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

insert into temp values(3.'Joe');# success
Copy the code
mysql> select * from temp;
+----+------+
| id | name |
+----+------+| | | 1 zhang SAN | | 2 |, dick, and harry | 3 | | zhang SAN +----+------+
3 rows in set (0.00 sec)
Copy the code
insert into temp values(4.null);# success


insert into temp values(null.'li3 qi2');# failure
ERROR 1048 (23000): Column 'id' cannot be null
Copy the code
mysql> select * from temp;
+----+------+
| id | name |
+----+------+| | | 1 zhang SAN | | 2 |, dick, and harry | 3 | threes | | | NULL | + 4----+------+
4 rows in set (0.00 sec)
Copy the code
Create two primary key constraints on a table
create table temp(
	id int primary key.name varchar(20) primary key); ERROR 1068 (42000): Multiple primary key definedCopy the code

For example:

  • Column level constraints
CREATE TABLE emp4(
    id INT PRIMARY KEY AUTO_INCREMENT ,
    NAME VARCHAR(20));Copy the code
  • Table level constraints
CREATE TABLE emp5(
    id INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(20),
    pwd VARCHAR(15),
    CONSTRAINT emp5_id_pk PRIMARY KEY(id));Copy the code

(2) add primary key constraint after create table

ALTER TABLEThe name of the tableADD PRIMARY KEY(Field list);The # field list can be a single field or multiple fields, or if multiple fields are compound primary keys, null values are not allowed
Copy the code
ALTER TABLE student ADD PRIMARY KEY (sid);
Copy the code
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);
Copy the code

4.5 About compound primary keys

create tableTable name (Field name data type, field name data type, field name data type, primarykey(field name1The field name2)  # indicates that the combination of fields 1 and 2 is unique, and there can be more fields
);
Copy the code
# students table
create table student(
    sid int primary key.# student id
    sname varchar(20)     # Student name
);

# the curriculum
create table course(
	cid int primary key.# Course Number
    cname varchar(20)     # Course Name
);

# course selection table
create table student_course(
	sid int,
    cid int,
    score int,
    primary key(sid,cid)  # compound primary key
);
Copy the code
insert into student values(1.'Joe'), (2.'bill');
insert into course values(1001.'Java'), (1002.'MySQL');
Copy the code
mysql> select * from student;
+-----+-------+
| sid | sname |
+-----+-------+| | 1 zhang SAN | | | |, dick, and harry + 2-----+-------+
2 rows in set (0.00 sec)

mysql> select * from course;
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | Java  |
| 1002 | MySQL |
+------+-------+
2 rows in set (0.00 sec)
Copy the code
insert into student_course values(1.1001.89), (1.1002.90), (2.1001.88), (2.1002.56);
Copy the code
mysql> select * from student_course;
+-----+------+-------+
| sid | cid  | score |
+-----+------+-------+| 1 | 1001 | 89 | | 1 | 1002 | | 90 | | 1001 | | 88 | | 1002 | | + to 56-----+------+-------+
4 rows in set (0.00 sec)
Copy the code
insert into student_course values(1.1001.100);
ERROR 1062 (23000): Duplicate entry '1-1001' for key 'PRIMARY'
Copy the code
mysql> desc student_course;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sid   | int(11) | NO   | PRI | NULL    |       |
| cid   | int(11) | NO   | PRI | NULL    |       |
| score | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Copy the code
  • Again, for example
CREATE TABLE emp6(
    id INT NOT NULL.NAME VARCHAR(20),
    pwd VARCHAR(15),
    CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);
Copy the code

4.6 Constraints on Deleting a Primary Key

alter tableThe name of the tabledrop primary key;
Copy the code

For example:

ALTER TABLE student DROP PRIMARY KEY;
Copy the code
ALTER TABLE emp5 DROP PRIMARY KEY;
Copy the code

Note: Delete primary key constraint, do not need to specify the primary key name, because a table has only one primary key, delete primary key constraint, non-empty still exist.

5. Increment columns: AUTO_INCREMENT

5.1 role

The value of a field is incremented

5.2 the keyword

auto_increment

5.3 Features and Requirements

(1) A table can have at most one self-growing column

(2) When you need to generate unique identifiers or sequential values, you can set self-growth

(3) Self-growing column constraint columns must be key columns (primary key columns, unique key columns)

(4) The data type of the column of the increment constraint must be an integer type

(5) If 0 and NULL are specified in the increment column, the increment will be based on the current maximum value; If the increment column manually specifies a specific value, the value is directly assigned to the specific value.

SET auto_increment_increment=3; Set the step length

(7) the start value can be set by manually inserting the value, that is, the start value is 10, followed by the insert null is 13,16

INSERT INTO tab_identity(id,NAME) VALUES(10.'john');
Copy the code

Error demo:

create table employee(
    eid int auto_increment,
    ename varchar(20));# ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key   
Copy the code
create table employee(
    eid int primary key,
    ename varchar(20) unique key auto_increment
);
# ERROR 1063 (42000): Incorrect column specifier for column 'ename' because ename is not an integer
Copy the code

5.4 How can I Specify an increment Constraint

(1) When building a table

create tableTable name (field name Data type PRIMARYkeyAuto_increment, the data type of the field nameunique key not null, field name data typeunique key, field name data typenot null defaultDefault value,);create tableTable name (field name Data typedefaultDefault value, field name data typeunique keyAuto_increment, the data type of the field namenot null defaultThe default value,, primarykey(field name));Copy the code
create table employee(
    eid int primary key auto_increment,
    ename varchar(20));Copy the code
mysql> desc employee;# Check the constraints set
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| eid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| ename | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
Copy the code

(2) After the table is built

alter tableThe name of the tablemodifyField Name Data Type AUTO_INCREMENT;Copy the code

Such as:

create table employee(
    eid int primary key ,
    ename varchar(20));Copy the code
alter table employee modify eid int auto_increment;
Copy the code
mysql> desc employee;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| eid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| ename | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
Copy the code

5.5 How Can I Delete an Autoadd Constraint

Alter table alter table auto_increment; Add an increment constraint to this field

alter tableThe name of the tablemodifyField name data type;Alter table auto_increment increment
Copy the code
alter table employee modify eid int;
Copy the code
mysql> desc employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eid   | int(11)     | NO   | PRI | NULL    |       |
| ename | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Copy the code

During development, assignments to primary key fields are not assigned; they are automatically incremented

5.6 New in MySQL 8.0 – Persistence of incrementing variables

MySQL > alter TABLE AUTO_INCREMENT= Max (primary key); alter table AUTO_INCREMENT= Max (primary key); This phenomenon can in some cases lead to business primary key conflicts or other hard-to-find problems. The following uses an example to compare whether an autoincrement is persisted in different versions. SQL > alter table select id from primary key; SQL > alter table select ID from primary key;

CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);
Copy the code

Insert four null values as follows:

INSERT INTO test1
VALUES(0), (0), (0), (0);
Copy the code

Query the data in table test1 and the result is as follows:

mysql> SELECT * FROM test1;
+----+
| id |
+----+| 1 | | | 2 | 3 | | | + 4----+
4 rows in set (0.00 sec)
Copy the code

Delete record 4 as follows:

DELETE FROM test1 WHERE id = 4;
Copy the code

Insert a null value again as follows:

INSERT INTO test1 VALUES(0);
Copy the code

Select * from test1;

mysql> SELECT * FROM test1;
+----+
| id |
+----+| 1 | | | 2 | 3 | | | + 5----+
4 rows in set (0.00 sec)
Copy the code

As you can see from the result, although the record with ID 4 is deleted, when the null value is inserted again, the deleted 4 is not reused, but 5 is allocated. Delete record 5 and the result is as follows:

DELETE FROM test1 where id=5;
Copy the code

Restart the database and reinsert a null value.

INSERT INTO test1 values(0);
Copy the code

Query the data in table test1 again and the result is as follows:

mysql> SELECT * FROM test1;
+----+
| id |
+----+| 1 | | | 2 | 3 | | | + 4----+
4 rows in set (0.00 sec)
Copy the code

As you can see from the result, the newly inserted 0 value is assigned 4, which should be assigned 6 according to the pre-restart operation logic. The main reason for the above results is that the increment primary key is not persisted. In MySQL 5.7, the allocation rules for auto-increment primary keys are determined by a counter inside InnoDB’s data dictionary, which is maintained only in memory and not persisted to disk. This counter is initialized when the database restarts.

In MySQL 8.0, the result of the last step of the above test steps is as follows:

mysql> SELECT * FROM test1;
+----+
| id |
+----+| 1 | | | 2 | 3 | | | + 6----+
4 rows in set (0.00 sec)
Copy the code

From the result, you can see that the increment variable has been persisted.

MySQL 8.0 persists counters that increment primary keys to redo logs. Every time a counter changes, it is written to the redo log. If the database is restarted, InnoDB initializes the memory value of the counter based on the information in the redo log.

6. FOREIGN KEY constraints

6.1 role

To restrict the referential integrity of a column in a table, a foreign key constraint is often used with a primary key constraint to ensure data consistency. That is, to ensure that the value of the field must come from the associated column of the primary table, a foreign key constraint is added to the secondary table to reference a column in the primary table

For example, the selection of the department in the employee table must be found in the corresponding part of the department table (the one-to-one correlation is demonstrated here).

6.2 the keyword

FOREIGN KEY

6.3 Primary and secondary tables/Parent and child tables

Primary table (parent table) : referenced table, referenced table

Slave table (sub table) : reference other tables, reference other tables

For example, the value of this field in the employee table refers to the department table. The department table is the primary table, and the employee table is the secondary table.

For example: Student table, course table, course selection table: students and courses on the course selection table should refer to the student table and the course schedule respectively. The student table and the course schedule are the master table, and the course selection table is the slave table.

Foreign keys:

  1. Requires foreign key relationships to be set on the slave table
  2. The foreign key column type of the secondary table must be the same or compatible with the associated column type of the primary table
  3. The associated column of the primary table must be a key (typically primary key or unique)
  4. When inserting data, insert the primary table first and then the secondary table
  5. When deleting data, delete the secondary table first and then the primary table

6.4 the characteristics of

(1) From the foreign key column of the table, must reference/reference the primary key of the primary table or unique constraint column

Why is that? Because the dependent/referenced value must be unique

(2) When creating a foreign key constraint, if the constraint is not named, the default name is not the column name. , you can also specify the foreign key constraint name.

Select * from primary table where foreign key constraint is specified

(4) When deleting a table, delete the secondary table first (or delete the foreign key constraint first) and then delete the primary table

(5) When the records of the primary table are referenced from the secondary table, the records of the primary table are not allowed to be deleted. To delete data, the data that depend on the records from the primary table must be deleted first

(6) Specify foreign key constraints in “from table”, and one table can create multiple foreign key constraints

(7) The name of the column referenced by the foreign key column of the secondary table and the column referenced by the primary table can be different, but the data type must be the same, and the logical meaning must be consistent. If the types are different, ERROR 1005 (HY000): Can’t create table’database.tablename'(errno: 150) is displayed when creating a child table.

For example, both indicate department numbers and are int types.

(8) When a foreign key constraint is created, the system creates a common index on the corresponding column by default. But the index name is the constraint name for the foreign key. (Very efficient query by foreign key)

(9) After the foreign key constraint is deleted, the corresponding index must be manually deleted

6.5 Adding Foreign Key Constraints

(1) When building a table

create tablePrimary table name (field1Data type primarykey, the field2Data type);create tableFrom table names (fields1Data type primarykey, the field2Data type, [CONSTRAINT< foreign key constraint name >]FOREIGN KEY(from a field in the table)referencesPrimary table name (referenced field));The datatype (a field from the table) must be the same as the datatype of the main table (the referenced field), and the same logical meaning
The field name of #(a field from the table) may or may not be the same as the field name of the main table (the referenced field)

-- FOREIGN KEY: Specifies columns in child tables at the table level
-- REFERENCES: Identifies columns in the parent table
Copy the code
create table dept( Table #
    did int primary key.# Department Number
    dname varchar(50)			# Department name
);

create table emp(# from the table
    eid int primary key.# Employee Number
    ename varchar(5),     # Employee name
    deptid int.# The department the employee works in
    foreign key (deptid) references dept(did)   Specify the foreign key constraint in the slave table
    Deptid = emp deptid = DEPTID = dept deptid = depTID); Select * from emP where dept = emP; Alter TABLE EMp; alter table DEPTCopy the code

(2) After the table is built

Typically, table to table associations are designed in advance, so foreign key constraints are defined at table creation time. However, if you need to change the design of the table (for example, add new fields, add new associations), but do not define foreign key constraints beforehand, you need to supplement the definition by modifying the table.

Format:

ALTER TABLEFrom the name of the tableADD [CONSTRAINTThe constraint name]FOREIGN KEY(from table field)REFERENCESPrimary table name (referenced field) [on update xx][on delete xx];
Copy the code

For example:

ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);
Copy the code

For example:

create table dept(
    did int primary key.# Department Number
    dname varchar(50)			# Department name
);

create table emp(
    eid int primary key.# Employee Number
    ename varchar(5),     # Employee name
    deptid int		  # The department the employee works in
);
Select * from table_name where foreign keys are not specified
Copy the code
alter table emp add foreign key (deptid) references dept(did);
Copy the code

6.6 Demo Problems

(1) Failure: not a key column

create table dept(
    did int ,		# Department Number
    dname varchar(50)	# Department name
);

create table emp(
    eid int primary key.# Employee Number
    ename varchar(5),     # Employee name
        deptid int.# The department the employee works in
    foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint cause did of dept is not a key column
Copy the code

(2) Failure: data types are inconsistent

create table dept(
	did int primary key.# Department Number
    dname varchar(50)			# Department name
);

create table emp(
	eid int primary key.# Employee Number
    ename varchar(5),     # Employee name
    deptid char.# The department the employee works in
    foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint because the depTID column is not the same as the did column in the main table
Copy the code

(3) success, both tables have the same field name

create table dept(
	did int primary key.# Department Number
    dname varchar(50)			# Department name
);

create table emp(
	eid int primary key.# Employee Number
    ename varchar(5),     # Employee name
    did int.# The department the employee works in
    foreign key (did) references dept(did)  
    Deptid = emp deptid = DEPTID = dept deptid = depTID
    There is no problem with the same name because the two diDs are in different tables
);
Copy the code

(4) Add, delete and modify problems

create table dept(
	did int primary key.# Department Number
    dname varchar(50)			# Department name
);

create table emp(
	eid int primary key.# Employee Number
    ename varchar(5),     # Employee name
    deptid int.# The department the employee works in
    foreign key (deptid) references dept(did)  
    Deptid = emp deptid = DEPTID = dept deptid = depTID
);
Copy the code
insert into dept values(1001.'Teaching Department');
insert into dept values(1003.'Finance Department');

insert into emp values(1.'Joe'.1001); SQL > select * from secondary table where id = 1001

insert into emp values(2.'bill'.1005);Failed to add secondary recordsERROR 1452 (23000): Cannot add orupdateAchild row: a foreign key constraint fails (`atguigudb`.`emp`.CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`Failed to add records from table EMp because main table DEPT does not have one1005departmentCopy the code
mysql> select * from dept;
+------+--------+
| did  | dname  |
+------+--------+Refer | | 1001 | | 1003 | | + the finance department------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+| | 1001 | | zhang SAN + 1-----+-------+--------+
1 row in set (0.00 sec)
Copy the code
update emp set deptid = 1002 where eid = 1;Failed to modify the slave tableERROR 1452 (23000): Cannot add orupdateAchild row(subtable records) : aforeign key constraintFails (foreign key constraint fails) (`atguigudb`.`emp`.CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`))  SQL > alter table depTID = 1002 depTID = 1002

update dept set did = 1002 where did = 1001;Failed to modify the main table
ERROR 1451 (23000): Cannot delete(delete)or updateAparent row(parent table records) : aforeign key constraint fails (`atguigudb`.`emp`.CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)) Table # department did 1001 field has been emp quotes, so the department table 1001 field cannot be modified.

update dept set did = 1002 where did = 1003;Since department 1003 is not referenced by the EMP table, it can be changed
Copy the code
delete from dept where did=1001; Failed to drop the primary table
ERROR 1451 (23000): Cannot delete(delete)or updateAparent row(parent table record) : aforeign key constraint fails (`atguigudb`.`emp`.CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`))  [emP] [emP] [EMP] [EMP] [EMP] [EMP] [EMP
Copy the code

Conclusion: The constraint relationship is for both parties

  • After the foreign key constraint is added, the modification and deletion data of the primary table are constrained

  • After the foreign key constraint is added, add and modify data from the table is constrained

  • To create a foreign key on a slave table, the primary table must exist

  • When deleting a primary table, the secondary table must be deleted first, or the relationships that reference the primary table by the foreign keys from the table must be deleted first

6.7 Constraint Levels

  • Cascade: When you update or delete records in the parent table, the Cascade deletes matching records in the child table

  • Set NULL: When updating or deleting records in the parent table, the matching columns in the child table are Set to NULL, but the foreign key columns in the child table cannot be not NULL

  • No Action: If there are matched records in the child table, update/ DELETE operations cannot be performed on the candidate key corresponding to the parent table

  • Restrict mode: Same as no Action, automatically checks foreign key constraints

  • Set default (may be blank in visualization tool SQLyog) : When the parent table changes, the child table sets the foreign key column to a default value that Innodb cannot recognize

If no level is specified, it is equal to Restrict mode.

For foreign key constraints, you are advised to set ON UPDATE CASCADE ON DELETE RESTRICT.

(1) Demo 1: on update cascade on delete set null

create table dept(
    did int primary key.# Department Number
    dname varchar(50)			# Department name
);

create table emp(
    eid int primary key.# Employee Number
    ename varchar(5),     # Employee name
    deptid int.# The department the employee works in
    foreign key (deptid) references dept(did)  on update cascade on delete set null
    # change the level of the delete operation to set null
);
Copy the code
insert into dept values(1001.'Teaching Department');
insert into dept values(1002.'Finance Department');
insert into dept values(1003.'Consulting');


insert into emp values(1.'Joe'.1001); Add this record to the department table with 1001 departments
insert into emp values(2.'bill'.1001);
insert into emp values(3.'Cathy'.1002);

Copy the code
mysql> select * from dept;

mysql> select * from emp;

Copy the code
Alter table 1002 alter table 1004 alter table 1004 alter table 1002 alter table 1004 alter table 1004
mysql> update dept set did = 1004 where did = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+------+--------+
| did  | dname  |
+------+--------+Refer | | 1001 | | 1003 | consulting | | 1004 | | the finance department# 1002 = 1004
+------+--------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+| 1 | zhang SAN | 1001 | | 2 | li si | 1001 | | 3 | fifty and 1004 | |# 1002 = 1004
+-----+-------+--------+
3 rows in set (0.00 sec)
Copy the code
Alter table alter table alter table alter table alter table alter table alter table alter table alter table
mysql> delete from dept where did = 1001; Query OK, 1 row affected (0.01sec) mysql>select * from dept;
+------+--------+
| did  | dname  | # Record department 1001 was deleted
+------+--------+1003 | | consulting | | 1004 | | + the finance department------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+| | NULL | 1 | zhang SANSelect * from depTID where depTID = null| | |, dick, and harry NULL | 2 | 3 | fifty and | | + 1004-----+-------+--------+
3 rows in set (0.00 sec)
Copy the code

(2) demo 2: on update set null on delete cascade

create table dept(
    did int primary key.# Department Number
    dname varchar(50)			# Department name
);

create table emp(
    eid int primary key.# Employee Number
    ename varchar(5),     # Employee name
    deptid int.# The department the employee works in
    foreign key (deptid) references dept(did)  on update set null on delete cascade
    Alter level set null, delete level set cascading delete level
);
Copy the code
insert into dept values(1001.'Teaching Department');
insert into dept values(1002.'Finance Department');
insert into dept values(1003.'Consulting');

insert into emp values(1.'Joe'.1001); Add this record to the department table with 1001 departments
insert into emp values(2.'bill'.1001);
insert into emp values(3.'Cathy'.1002);
Copy the code
mysql> select * from dept;
+------+--------+
| did  | dname  |
+------+--------+Refer | | 1001 | | 1002 | department | | 1003 | consulting | +------+--------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+| 1 | zhang SAN | 1001 | | 2 | li si | 1001 | | 3 | fifty and | | + 1002-----+-------+--------+
3 rows in set (0.00 sec)
Copy the code
Alter table set primary table to null
mysql> update dept set did = 1004 where did = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+------+--------+
| did  | dname  |
+------+--------+Refer | | 1001 | | 1003 | consulting | | 1004 | | the finance departmentDid = 1002
+------+--------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+| 1 | zhang SAN | 1001 | | 2 | li si | 1001 | | 3 | fifty | NULL |Alter table 1002 alter table 1002 alter table 1002 alter table 1002
+-----+-------+--------+
3 rows in set (0.00 sec)
Copy the code
Select * from main table where rows 1001 and 1001 were dropped
mysql> delete from dept where did=1001;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
+------+--------+
| did  | dname  | Delete department 1001 from department table
+------+--------+1003 | | consulting | | 1004 | | + the finance department------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |# The original employee of department 1001 has also been deleted
+-----+-------+--------+| 3 | fifty | NULL | +-----+-------+--------+
1 row in set (0.00 sec)

Copy the code

(3) demo: On update cascade on delete cascade

create table dept(
    did int primary key.# Department Number
    dname varchar(50)			# Department name
);

create table emp(
    eid int primary key.# Employee Number
    ename varchar(5),     # Employee name
    deptid int.# The department the employee works in
    foreign key (deptid) references dept(did)  on update cascade on delete cascade
    Set the change level to cascade change level and set the delete level to cascade change level
);
Copy the code
insert into dept values(1001.'Teaching Department');
insert into dept values(1002.'Finance Department');
insert into dept values(1003.'Consulting');

insert into emp values(1.'Joe'.1001); Add this record to the department table with 1001 departments
insert into emp values(2.'bill'.1001);
insert into emp values(3.'Cathy'.1002);
Copy the code
mysql> select * from dept;
+------+--------+
| did  | dname  |
+------+--------+Refer | | 1001 | | 1002 | department | | 1003 | consulting | +------+--------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+| 1 | zhang SAN | 1001 | | 2 | li si | 1001 | | 3 | fifty and | | + 1002-----+-------+--------+
3 rows in set (0.00 sec)
Copy the code
Mysql > alter table table_name alter table table_name
mysql> update dept set did = 1004 where did = 1002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+------+--------+
| did  | dname  |
+------+--------+Refer | | 1001 | | 1003 | consulting | | 1004 | | the finance department# department 1002 changed to 1004
+------+--------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+| 1 | zhang SAN | 1001 | | 2 | li si | 1001 | | 3 | fifty and 1004 | |# Cascade changes
+-----+-------+--------+
3 rows in set (0.00 sec)
Copy the code
Select * from main table where rows 1001 and 1001 were dropped
mysql> delete from dept where did=1001;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
+------+--------+
| did  | dname  | Department #1001 has been removed
+------+--------+1003 | | consulting | | 1004 | | + the finance department------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |  Employees in #1001 were also deleted
+-----+-------+--------+| 3 | fifty and | | + 1004-----+-------+--------+
1 row in set (0.00 sec)
Copy the code

6.8 Deleting Foreign Key Constraints

The process is as follows:

(1) The first step is to check the constraint name and delete the foreign key constraintSELECT * FROM information_schema.table_constraints WHERE table_name = 'Table name';Check the constraint name of a table

ALTER TABLEFrom the name of the tableDROP FOREIGN KEYForeign key constraint name; (2) The second step is to check the index name and delete the index. (Note that it can only be manually deleted)SHOW INDEX FROMTable name;Select * from table_name where table_name = 'index'

ALTER TABLEFrom the name of the tableDROP INDEXIndex name;Copy the code

For example:

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';

mysql> alter table emp drop foreign key emp_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Copy the code
mysql> show index from emp;

mysql> alter table emp drop index deptid;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  show index from emp;
Copy the code

6.9 Development Scenarios

Question 1: If two tables have a relationship (one-to-one, one-to-many), for example, an employee table and a department table (one-to-many), do they have to create a foreign key constraint between them?

Answer: No

Question 2: What is the difference between a build and an unbuild foreign key constraint?

A: Foreign key constraint, your operations (create table, delete table, add, modify, delete table) are limited, syntactically limited. For example, it is not possible to add an employee to the employee table because the value of its department is not found in the department table.

Without foreign key constraints, your operations (create table, delete table, add, modify, delete table) are not limited. To ensure data referential integrity, you can only rely on the programmer’s self-awareness, or in Java program restrictions. For example, in the employee table, you can add information for an employee whose department is specified as a department that does not exist at all.

Question 3: Is there a relationship between the build and unbuild foreign key constraints and the query?

A: no

In MySQL, foreign key constraints have a cost, consuming system resources. For large concurrent SQL operations, this may not be appropriate. A central database for a large web site, for example, can be very slow due to the overhead of foreign key constraints. So, MySQL allows you to do the logic of checking data consistency at the application level without using the system’s native foreign key constraints. In other words, even if you don’t use foreign key constraints, you need to find ways to implement foreign key constraints by applying additional logic at the application level to ensure data consistency.

6.10 Ali Development Specification

[Mandatory] Do not use foreign keys and cascading. All foreign key concepts must be resolved at the application layer.

Note (Concept description) Student_id in the student table is the primary key, and student_id in the score table is the foreign key. If the student_ID in the student table is updated, the student_id in the score table is updated in cascade mode. Foreign keys and cascading update are suitable for low-concurrency single machine, but not for distributed and high-concurrency cluster. Cascading update is strongly blocked, and there is the risk of database update storm. Foreign keys affect database insert speed.

7. The CHECK constraint

7.1 role

Check whether the value of a field conforms to xx, generally referring to the range of values

7.2 the keyword

CHECK

7.3 Note: MySQL 5.7 does not support MySQL 5.7

MySQL5.7 can use the check constraint, but the check constraint has no effect on data validation. Data is added without any errors or warnings

However, the check constraint is now available in MySQL 8.0.

create table employee(
	eid int primary key,
    ename varchar(5),
    gender char check ('male' or 'woman'));Copy the code
insert into employee values(1.'Joe'.'monster');
Copy the code
mysql> select * from employee;
+-----+-------+--------+
| eid | ename | gender |
+-----+-------+--------+| | | zhang SAN demon | + 1-----+-------+--------+
1 row in set (0.00 sec)
Copy the code
  • Again, for example
CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id));Copy the code
  • Again, for example
age tinyint check(age >20) or sexchar(2) check(sex in(' male ', 'female')Copy the code
  • Again, for example
CHECK(height>=0 AND height<3)
Copy the code

8. The DEFAULT constraints

8.1 role

Specifies a default value for a field/column, once set, if the field is not explicitly assigned when data is inserted.

8.2 the keyword

DEFAULT

8.3 How Can I Add Default Values to fields

(1) When building a table

create tableTable name (field name Data type PRIMARYkey, field name data typeunique key not null, field name data typeunique key, field name data typenot null defaultDefault value,);create tableTable name (field name Data typedefaultDefault value, field name data typenot null defaultDefault value, field name data typenot null defaultThe default value is primarykey(field name),unique key(field name)); Note: Default constraints are generally not added to unique and primary key columnsCopy the code
create table employee(
	eid int primary key,
    ename varchar(20) not null,
    gender char default 'male',
    tel char(11) not null default ' ' The default is an empty string
);
Copy the code
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+| | eid | int (11) NO | PRI | NULL | | | ename | varchar (20) | NO | | NULL | | | gender | char (1) | YES | | male | | | tel |  char(11) | NO | | | | +--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Copy the code
insert into employee values(1.'Wang Fei'.'male'.'13700102535'); # success
Copy the code
mysql> select * from employee;
+-----+-------+--------+-------------+
| eid | ename | gender | tel         |
+-----+-------+--------+-------------+| 1 | Wang Fei | | | + 13700102535-----+-------+--------+-------------+
1 row in set (0.00 sec)
Copy the code
insert into employee(eid,ename) values(2.'day that students'); # success
Copy the code
mysql> select * from employee;
+-----+-------+--------+-------------+
| eid | ename | gender | tel         |
+-----+-------+--------+-------------+| 1 | Wang Fei | | | 13700102535 | | 2 days qi male | | | +-----+-------+--------+-------------+
2 rows in set (0.00 sec)
Copy the code
insert into employee(eid,ename) values(3.'two tigers');
#ERROR 1062 (23000): Duplicate entry '' for key 'tel'  
If the TEL does not have a unique constraint, you can add it successfully
Copy the code

For example:

CREATE TABLE myemp(
id INT AUTO_INCREMENT PRIMARY KEY.NAME VARCHAR(15),
salary DOUBLE(10.2) DEFAULT 2000
);
Copy the code

(2) After the table is built

alter tableThe name of the tablemodifyField name Data typedefaultThe default value;# if the field has a non-null constraint and you keep the non-null constraint, then when adding the default value constraint, you must keep the non-null constraint, otherwise the non-null constraint will be removed
If a field has a default value and you want to keep it, use the modify statement to keep the default value constraint, otherwise delete it
alter tableThe name of the tablemodifyField name Data typedefaultThe default valuenot null;
Copy the code
create table employee(
	eid int primary key,
    ename varchar(20),
    gender char,
    tel char(11) not null
);
Copy the code
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid    | int(11)     | NO   | PRI | NULL    |       |
| ename  | varchar(20) | YES  |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
| tel    | char(11)    | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Copy the code
alter table employee modify gender char default 'male';  Add a default constraint to the GENDER field
alter table employee modify tel char(11) default ' '; Add default constraint to TEL field
Copy the code
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+| | eid | int (11) NO | PRI | NULL | | | ename | varchar (20) | YES | | NULL | | | gender | char (1) | YES | | male | | | tel | char(11) | YES | | | | +--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Copy the code
alter table employee modify tel char(11) default ' '  not null;Add a default constraint to the TEL field and leave the non-null constraint
Copy the code
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+| | eid | int (11) NO | PRI | NULL | | | ename | varchar (20) | YES | | NULL | | | gender | char (1) | YES | | male | | | tel | char(11) | NO | | | | +--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Copy the code

8.4 How Can I Delete Constraints on Default Values

alter tableThe name of the tablemodifyField name data type;# delete default constraint and do not retain non-null constraint

alter tableThe name of the tablemodifyField name Data typenot null; Delete the default constraint and leave the non-null constraint
Copy the code
alter table employee modify gender char; # remove the gender field default constraint, if there is a non-null constraint, remove it as well
alter table employee modify tel char(11)  not null;Delete the default tel constraint and leave the non-null constraint
Copy the code
mysql> desc employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid    | int(11)     | NO   | PRI | NULL    |       |
| ename  | varchar(20) | YES  |     | NULL    |       |
| gender | char(1)     | YES  |     | NULL    |       |
| tel    | char(11)    | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Copy the code

9. The interview

SQL > create table not null default (default 0)

Answer: Do not want null values in the table.

Interview 2. Why don’t you want a null value

Answer :(1) not easy to compare. Null is a special value. Only is NULL and IS not NULL can be used for comparison. When an operator is encountered, it usually returns NULL.

(2) Low efficiency. Impact improves indexing. Therefore, we tend to build tables with not null default “or default 0

Alter table AUTO_INCREMENT increment (1); In MySQL, the default AUTO_INCREMENT initialvalue is 1. When a new record is added, the field value is automatically incremented by 1. When setting the AUTO_INCREMENT attribute, you can also specify the value of the increment field of the first inserted record. In this way, the value of the increment field of the newly inserted record increases from the initial value. For example, if the id of the first inserted record is set to 5, the ID of the subsequent inserted records increases from 6. When adding primary key constraints, you often need to set the field to automatically add attributes.

Interview 4. Not every table can have an arbitrary choice of storage engine? FOREIGN KEY constraints cannot be used across engines.

MySQL supports multiple storage engines, and each table can specify a different storage engine. Note that foreign key constraints are used to ensure data referential integrity. If different storage engines are specified, foreign key constraints cannot be created between tables. So the choice of storage engine is not entirely arbitrary.