Python database (field constraints, table to table…
January 26, 2024
by Alison Perez
No Comments
10.6 Constraints 10.61 not null 、default
[url=] [/url]
create
table
t15( id
int
, name
char
(
16
)
not
null
, sex enum(
‘
male
‘
.
‘
female
‘
.
‘
other
‘
)
not
null
default
“male”); #
alter
table
t15 modify name
char
(
16
)
not
null
;
insert
into
t15(id,name)
values
(
1
.
‘
egon1
‘
), (
2
.
‘
egon2
‘
), (
3
.
‘
egon3
‘
); mysql
>
desc
t15;
+
—
—–+——————————-+——+—–+———+——-+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
—
—–+——————————-+——+—–+———+——-+
|
id
|
int
(
11
)
|
YES
|
|
NULL
|
|
|
name
|
char
(
16
)
|
NO
|
|
NULL
|
|
|
sex
|
enum(
‘
male
‘
.
‘
female
‘
.
‘
other
‘
)
|
NO
|
|
male
|
|
+
—
—–+——————————-+——+—–+———+——-+
mysql
>
select
*
from
t15;
+
—
—-+——-+——+
|
id
|
name
|
sex
|
+
—
—-+——-+——+
|
1
|
egon1
|
male
|
|
2
|
egon2
|
male
|
|
3
|
egon3
|
male
|
+
—
—-+——-+——+
[url=] [/url]
10.62 the unique
Mysql has a special data structure called a key, which can reduce the number of I/OS and speed up the query efficiency. The primary key not only accelerates the query, but also adds a constraint function: Innodb storage engine organizes all data in a table by the value of the primary key field, so an INNDOB table must have only one primary key, usually the ID field of the table
Unique: limits the uniqueness of a field’s value
[url=]
[/url]
# single column unique
create
table
t16( id
int
unique
, name
char
(
16
)); # union unique (cannot be identical)
create
table
server( id
int
unique
, ip
char
(
15
), port
int
.
unique
(ip,port)); mysql
>
desc
server;
+
—
—–+———-+——+—–+———+——-+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
—
—–+———-+——+—–+———+——-+
|
id
|
int
(
11
)
|
YES
|
UNI
|
NULL
|
|
|
ip
|
char
(
15
)
|
YES
|
MUL
|
NULL
|
|
|
port
|
int
(
11
)
|
YES
|
|
NULL
|
|
+
—
—–+———-+——+—–+———+——-+
insert
into
server
values
(
1
.
‘
1.1.1.1
‘
.
3306
), (
2
.
‘
1.1.1.1
‘
.
3307
), (
3
.
‘
1.1.1.2
‘
.
3306
); mysql
>
select
*
from
server;
+
—
—-+———+——+
|
id
|
ip
|
port
|
+
—
—-+———+——+
|
1
|
1.1
.
1.1
|
3306
|
|
2
|
1.1
.
1.1
|
3307
|
|
3
|
1.1
.
1.2
|
3306
|
+
—
—-+———+——+
[url=]
[/url]
10.63 primary key
Primary key: From a constraint perspective alone, the primary key is equivalent to not NULL + unique Emphasize:There must be only one primary key in a table idField, and should put idField as primary key
[url=]
[/url]
create
table
t17( id
int
primary
key
, name
char
(
16
), age
int
, sex
char
(
6
))engine
=
innodb; # join primary key (not exactly the same, but not empty)
create
table
t19( ip
char
(
15
), port
int
.
primary
key
(ip,port)); mysql
>
desc
t19;
+
—
—–+———-+——+—–+———+——-+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
—
—–+———-+——+—–+———+——-+
|
ip
|
char
(
15
)
|
NO
|
PRI
|
|
|
|
port
|
int
(
11
)
|
NO
|
PRI
|
0
|
|
+
—
—–+———-+——+—–+———+——-+
[url=]
[/url]
10.64 auto_increment
The default start position is 1, and the step size is 1
[url=]
[/url]
#
primary
key
auto_increment
create
table
t20( id
int
primary
key
auto_increment, name
char
(
16
))engine
=
innodb; mysql
>
insert
t20(name)
values
(
‘
egon1
‘
); mysql
>
insert
t20(name)
values
(
‘
egon2
‘
); mysql
>
select
*
from
t20;
+
—
–+——-+
|
id
|
name
|
+
—
–+——-+
|
1
|
egon1
|
|
2
|
egon2
|
+
—
–+——-+
[url=]
[/url]
10.7 table relationships 10.71 Many to One (Foreign Key)
1. Disadvantages of having all your data in one table:
The organization of tables is complex and unclear
Waste of space
Poor expansibility
Part1:1, first stand in the Angle of the left table EMP 2, go to find whether the left table EMP records corresponding to a record of the right table DEP
Part2:1, deP, deP, deP, deP, deP, deP, deP, deP, deP, deP Add a dep_ID field to the EMP table that points to the ID field of the DEP table
4. What effect will foreign Key bring?
Constraint 1: Before creating a table, create the associated table first depTo create the associated table EMP
[url=]
[/url]
create
table
dep( id
int
primary
key
auto_increment, dep_name
char
(
10
), dep_comment
char
(
60
)); mysql
>
desc
dep;
+
—
———–+———-+——+—–+———+—————-+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
—
———–+———-+——+—–+———+—————-+
|
id
|
int
(
11
)
|
NO
|
PRI
|
NULL
|
auto_increment
|
|
dep_name
|
char
(
10
)
|
YES
|
|
NULL
|
|
|
dep_comment
|
char
(
60
)
|
YES
|
|
NULL
|
|
+
—
———–+———-+——+—–+———+—————-+
create
table
emp( id
int
primary
key
auto_increment, name
char
(
16
), gender enum(
‘
male
‘
.
‘
female
‘
)
not
null
default
‘
male
‘
, dep_id
int
.
foreign
key
(dep_id)
references
dep(id)); mysql
>
desc
emp;
+
—
——+———————–+——+—–+———+—————-+
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
+
—
——+———————–+——+—–+———+—————-+
|
id
|
int
(
11
)
|
NO
|
PRI
|
NULL
|
auto_increment
|
|
name
|
char
(
16
)
|
YES
|
|
NULL
|
|
|
gender
|
enum(
‘
male
‘
.
‘
female
‘
)
|
NO
|
|
male
|
|
|
dep_id
|
int
(
11
)
|
YES
|
MUL
|
NULL
|
|
+
—
——+———————–+——+—–+———+—————-+
[url=]
[/url]
Constraint 2: Before inserting a record, the associated table must be inserted first depTo insert the associated table EMP
[url=]
[/url]
insert
into
dep(dep_name,dep_comment)
values
(
‘
Refer sb
‘
.
‘
Sb tutored students and taught Python courses
‘
), (
‘
The Ministry of Foreign Affairs
‘
.
‘
ambassador
‘
), (
‘
Nb technology
‘
.
‘
Nb department with limited technical capacity
‘
); mysql
>
select
*
from
dep;
+
—
–+————-+——————————————-+
|
id
|
dep_name
|
dep_comment
|
+
—
–+————-+——————————————-+
|
1
|
Refer sb
|
Sb tutored students and taught Python courses
|
|
2
|
The Ministry of Foreign Affairs
|
ambassador
|
|
3
|
Nb technology
|
Nb department with limited technical capacity
|
+
—
–+————-+——————————————-+
insert
into
emp(name,gender,dep_id)
values
(
‘
alex
‘
.
‘
male
‘
.
1
), (
‘
egon
‘
.
‘
male
‘
.
2
), (
‘
lxx
‘
.
‘
male
‘
.
1
), (
‘
wxx
‘
.
‘
male
‘
.
1
), (
‘
wenzhou
‘
.
‘
female
‘
.
3
); mysql
>
select
*
from
emp;
+
—
–+———+——–+——–+
|
id
|
name
|
gender
|
dep_id
|
+
—
–+———+——–+——–+
|
1
|
alex
|
male
|
1
|
|
2
|
egon
|
male
|
2
|
|
3
|
lxx
|
male
|
1
|
|
4
|
wxx
|
male
|
1
|
|
5
|
wenzhou
|
female
|
3
|
+
—
–+———+——–+——–+
[url=]
[/url]
Constraint 3: Both updates and deletes need to take into account the relationship between the association and the associated (deP table ID cannot be changed directly) Solution:
View Code
10.72 Many-to-many (Foreign Key)
The many-to-many relationship between two tables is a two-way many-to-one relationship, called many-to-many. Create a third table with one field for the ID of the left table of FK and one field for the ID of the right table of FK
[url=]
[/url]
create
table
author( id
int
primary
key
auto_increment, name
char
(
16
));
create
table
book( id
int
primary
key
auto_increment, bname
char
(
16
), price
int
);
insert
into
author(name)
values
(
‘
egon
‘
), (
‘
alex
‘
), (
‘
wxx
‘
); mysql
>
select
*
from
author;
+
—
–+——+
|
id
|
name
|
+
—
–+——+
|
1
|
egon
|
|
2
|
alex
|
|
3
|
wxx
|
+
—
–+——+
insert
into
book(bname,price)
values
(
‘
Python from the beginning to the ground
‘
.
200
), (
‘
Sunflower cutting to mastery
‘
.
800
), (
‘
Nine Yin true through
‘
.
500
), (
‘
Sun alkaloids
‘
.
100
); mysql
>
select
*
from
book;
+
—
–+—————————–+——-+
|
id
|
bname
|
price
|
+
—
–+—————————–+——-+
|
1
|
Python from the beginning to the ground
|
200
|
|
2
|
Sunflower cutting to mastery
|
800
|
|
3
|
Nine Yin true through
|
500
|
|
4
|
Sun alkaloids
|
100
|
+
—
–+—————————–+——-+
create
table
author2book( id
int
primary
key
auto_increment, author_id
int
, book_id
int
.
foreign
key
(author_id)
references
author(id)
on
update
cascade
on
delete
cascade
.
foreign
key
(book_id)
references
book(id)
on
update
cascade
on
delete
cascade
);
insert
into
author2book(author_id,book_id)
values
(
1
.
3
), (
1
.
4
), (
2
.
2
), (
2
.
4
), (
3
.
1
), (
3
.
2
), (
3
.
3
), (
3
.
4
); mysql
>
select
*
from
author2book;
+
—
–+———–+———+
|
id
|
author_id
|
book_id
|
+
—
–+———–+———+
|
1
|
1
|
3
|
|
2
|
1
|
4
|
|
3
|
2
|
2
|
|
4
|
2
|
4
|
|
5
|
3
|
1
|
|
6
|
3
|
2
|
|
7
|
3
|
3
|
|
8
|
3
|
4
|
+
—
–+———–+———+
[url=]
[/url]
One to one (Unique + Foreign key)
[url=]
[/url]
One-to-one: A record in the left table uniquely corresponds to a record in the right table and vice versa
create
table
Customer (# create the referenced table ID first
int
primary
key
auto_increment, name
char
(
20
)
not
null
, qq
char
(
10
)
not
null
, phone
char
(
16
)
not
null
);
create
table
student( id
int
primary
key
auto_increment, class_name
char
(
20
)
not
null
, customer_id
int
unique
# This field must be unique, one-to-one
foreign
key
(customer_id)
references
Customer (ID) # foreign key must be unique
on
delete
cascade
on
update
cascade
);
insert
into
customer(name,qq,phone)
values
(
‘
Li Fei machine
‘
.
‘
31811231
‘
.
13811341220
), (
‘
The king of the cannon
‘
.
‘
123123123
‘
.
15213146809
), (
‘
Keep a grenade
‘
.
‘
283818181
‘
.
1867141331
), (
‘
Wu tanks
‘
.
‘
283818181
‘
.
1851143312
), (
‘
The rockets win
‘
.
‘
888818181
‘
.
1861243314
), (
‘
War mines
‘
.
‘
112312312
‘
.
18811431230
); mysql
>
select
*
from
customer;
+
—
–+———–+———–+————-+
|
id
|
name
|
qq
|
phone
|
+
—
–+———–+———–+————-+
|
1
|
Li Fei machine
|
31811231
|
13811341220
|
|
2
|
The king of the cannon
|
123123123
|
15213146809
|
|
3
|
Keep a grenade
|
283818181
|
1867141331
|
|
4
|
Wu tanks
|
283818181
|
1851143312
|
|
5
|
The rockets win
|
888818181
|
1861243314
|
|
6
|
War mines
|
112312312
|
18811431230
|
+
—
–+———–+———–+————-+
insert
into
student(class_name,customer_id)
values
(
‘
python
‘
.
3
), (
‘
java
‘
.
4
), (
‘
c++
‘
.
5
); mysql
>
select
*
from
student;
+
—
–+————-+————-+
|
id
|
class_name
|
customer_id
|
+
—
–+————-+————-+
|
1
|
python
|
3
|
|
2
|
java
|
4
|
|
3
|
c
++
|
5
|
+
—
–+————-+————-+
[url=]
[/url]
More technical information can be obtained from itheimaGZ
[/url]
