foreign key 會帶來什麼效果 ？
1 在建立表時 先建被關聯的表 table1，才能建立被關聯的表table2
2 在插入記錄時，必須先插入被關聯的表table1 才能插入被關聯的表table2，
3 更新和删除都需要考慮到關聯被關聯的關系 》》》同步更新與删除
4 表之間的一對多和多對多以及一對一關系都是用的foreign key差別在于如何使用以及其他條件限制
表與表之間一對多關系(Foreign Key)
一：建立被關聯表
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment,char(60)
);
二：建立關聯表
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)
);
三：插入被關聯表
insert into dep(dep_name,dep_comment) values
('教學部','輔導學生學習，教授python課程'),
('銷售部','銷售産品課程'),
('技術部','技術能力有限部門');
四：插入關聯表
insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',2),
('wxx','male',1),
('cxx','female',3);
五：更新被關聯表
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
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) reference dep(id)
on update cascade
on delete cascade
);
insert into dep(dep_name,dep_comment)values
('教學部','輔導學生學習，教授python課程')
('銷售部','銷售産品課程')，
('技術部','技術能力有限部門');
insert into emp(name,gender,dep_id) values
('alex','male',1),
('egon','male',2),
('lxx','male',2),
('wxx','male',1),
('cxx','female',3);
表與表之間多對多關系(Foreign Key)
create table author(
id int primary key auto_increment,
name char(16)
);
create table book(
id int primary key auto_increment,
book_name char(16),
price int,
);
insert into author(name) vaules
('egon'),
('alex'),
('wxx');
insert into book(book_name,price) values
('python入門書',200),
('python提高書',400),
('python核心書',600),
('python自通書',800);
create table author2book(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) reference author(id)
on update cascade
on delete cascade,
foreign key(book_id) reference 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);
表與表之間一對一的關系(Foreign Key):
create table customer(
id 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(10) not null,
customer_id int unique,
foreign key(customer_id) reference customer(id)
on delete cascade
on update cascade
);