Python Day43 Table Integrity constraints

An introduction

Constraints are the same as the width of the data type, which are optional parameters

Role: To ensure the integrity and consistency of data
Mainly divided into:

PRIMARY Key (PK) identifies the    field as the primary key of the table and can uniquely identify the record foreign key (FK) identifies the    field as the foreign key of the table    not null identity The field cannot be empty unique key (UK)    The value that identifies the field is unique auto_increment the    value of the field is automatically grown (integer type, and the primary key)    default for this field is set to the defaults unsigned unsigned zerofill using 0 padding

Description

1. Allow null, default NULL, set not NULL, field not allowed to be empty, must be assigned a value of 2. Whether a field has a default value, the default value is NULL, if the record is inserted without assigning a value to the field, this field uses the default value of the Sex enum (' Male ', ' female ') not null default ' male ' age int unsigned NOT NULL Default 20 must be positive (unsigned) is not allowed to be null defaults to 203. Is the key primary key primary key foreign key foreign key index (Index,unique ...)

Two not NULL vs. default

Nullable, NULL indicates NULL, non-string
Not null-non-nullable
Null-Nullable

Default value, you can specify a default value when creating a column, and automatically add a default value when inserting data if it is not actively set
CREATE TABLE TB1 (
Nid int not null Defalut 2,
num int NOT NULL
)

Three unique

============ Set Unique constraint unique=============== method one: Create TABLE department1 (ID int,name varchar () unique,comment varchar ( 100); method two: Create TABLE Department2 (ID int,name varchar), comment varchar (+), constraint uk_name unique (name)); mysql> INSERT INTO department1 values (1, ' IT ', ' technology '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into department1 values (1, ' IT ', ' technology '); ERROR 1062 (23000): Duplicate entry ' IT ' for key ' name '

Four primary key

The value of the primary key field is not null and unique

In a table you can:

Single-row key
Multi-column Master key (composite primary key)

But there can only be one primary key in a table primary key

============ single-row key ===============
#方法一: Not Null+unique
CREATE TABLE Department1 (
ID int not NULL unique, #主键
Name varchar () is not null unique,
Comment varchar (100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| ID | Int (11) | NO | PRI | NULL | |
| name | varchar (20) | NO | UNI | NULL | |
| Comment | varchar (100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
Rows in Set (0.01 sec)

#方法二: Use primary key after a field
CREATE TABLE Department2 (
ID int primary KEY, #主键
Name varchar (20),
Comment varchar (100)
);

mysql> desc Department2;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| ID | Int (11) | NO | PRI | NULL | |
| name | varchar (20) | YES | | NULL | |
| Comment | varchar (100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
Rows in Set (0.00 sec)

#方法三: Primary key is defined separately after all fields
CREATE TABLE Department3 (
ID int,
Name varchar (20),
Comment varchar (100),
Constraint Pk_name primary key (ID); #创建主键并为其命名pk_name

mysql> desc Department3;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| ID | Int (11) | NO | PRI | NULL | |
| name | varchar (20) | YES | | NULL | |
| Comment | varchar (100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
Rows in Set (0.01 sec)

Single-Column primary key

Five Auto_increment

Constraint fields are autogrow, constrained fields must be constrained by key at the same time

#不指定id, it grows automatically
CREATE TABLE Student (
ID int primary KEY auto_increment,
Name varchar (20),
Sex enum (' Male ', ' female ') default ' male '
);

mysql> desc Student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| ID | Int (11) | NO | PRI | NULL | auto_increment |
| name | varchar (20) | YES | | NULL | |
| sex | Enum (' Male ', ' female ') | YES | | Male | |
+-------+-----------------------+------+-----+---------+----------------+
mysql> INSERT into student (name) values
(' Egon '),
(' Alex ')
;

Mysql> select * from student;
+----+------+------+
| ID | name | sex |
+----+------+------+
| 1 | Egon | Male |
| 2 | Alex | Male |
+----+------+------+

#也可以指定id
mysql> INSERT into student values (4, ' ASB ', ' female ');
Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into student values (7, ' WSB ', ' female ');
Query OK, 1 row Affected (0.00 sec)

Mysql> select * from student;
+----+------+--------+
| ID | name | sex |
+----+------+--------+
| 1 | Egon | Male |
| 2 | Alex | Male |
| 4 | ASB | Female |
| 7 | WSB | Female |
+----+------+--------+

#对于自增的字段, after deleting with delete and inserting the value, the field continues to grow as it was before it was deleted
mysql> Delete from student;
Query OK, 4 rows Affected (0.00 sec)

Mysql> select * from student;
Empty Set (0.00 sec)

mysql> INSERT into student (name) VALUES (' YSB ');
Mysql> select * from student;
+----+------+------+
| ID | name | sex |
+----+------+------+
| 8 | YSB | Male |
+----+------+------+

#应该用truncate清空表, the truncate is a direct emptying of the table, and it is used when deleting a large table, rather than deleting the record one by one.
Mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT into student (name) VALUES (' Egon ');
Query OK, 1 row affected (0.01 sec)

Mysql> select * from student;
+----+------+------+
| ID | name | sex |
+----+------+------+
| 1 | Egon | Male |
+----+------+------+
Row in Set (0.00 sec)

Six foreign key

A quick understanding of foreign key

The Employee Information table has three fields: work number name Department

The company has 3 departments, but there are 100 million of employees, that means the department this field needs to be repeated storage, the longer the department name, the more wasted

Workaround:

We can definitely define a departmental table.

The Employee Information table is then associated with the table, and how it is associated, that is, foreign key

Two how to find out the relationship between two tables

Analysis Step: #1, stand in the angle of the left table to find out if the left table of multiple records can correspond to a record of the right table, if so, then prove a field of the left table foreign key right Table a field (usually ID) #2, and then stand in the angle of the right table to find whether the right table of multiple records can correspond to a record If it is, then prove a field in the right table foreign key to the left table a field (usually an ID) #3, Summary: #多对一: If only step 1 is established, then is the left table many to one right table if only step 2 is established, then the right table many to one left table # Many-to-many if steps 1 and 2 are also established, The two tables are proved to be one-way multi-pair, that is, many-to-many, you need to define a relational table of the two tables to specifically store the relationship between them # one-to-one: if 1 and 2 are not true, but a record of the left table only corresponds to a record of the right table, and vice versa. This is simple, that is, in the left table foreign key to the right table based on the left table's foreign key field is set to a unique can

Three establishing a relationship between tables

#一对多或称为多对一三张表: Publishing house, author information, book one-to-many (or many to one): A publishing house can publish multiple books related ways: foreign key

#多对多三张表: Publishing house, author information, book many-to-many: an author can write more than one book, a book can also have multiple authors, two-way to many, that is, many-to-many associations: foreign key+ a new table

#一对一两张表: Student table and Customer table one-to: A student is a client, a client may become a school, that is, one-to-one relationship: foreign key+unique

#一定是student来foreign key table customer, this ensures that: #1 student must be a customer, #2 customer is not necessarily a student, but it is possible to become a student CREATE TABLE customer (ID int primary key Auto_increment,name varchar () not NULL), CREATE TABLE student (ID int PRIMARY key auto_increment,name varchar () NOT NULL , class_name varchar () NOT NULL default ' Python automation ', level int default 1,customer_id int unique, #该字段一定要是唯一的foreign key (CU STOMER_ID) references customer (ID) #外键的字段一定要保证uniqueon Delete cascadeon update cascade); #增加客户insert into customer (name) VALUES (' Lee aircraft '), (' King Cannon '), (' Grenade '), (' Wu Tank '), (' Win Rockets '), (' War mines '), #增加学生insert into student (name,customer_id) VALUES (' Lee airplane ', 1), (' King Cannon ', 2);

Python Day43 Table Integrity constraints