一对多

一对多

# 一对多:外键必须放在多的一方,此时外键值不唯一
# 出版社(publish): id,name,address,phone
create table publish(
    id int primary key auto_increment,
    name varchar(64),
    address varchar(256),
    phone char(20)
);

# 书(book):id,name,price,publish_id, author_id
create table book(
    id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int,  # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
);

# 增:先增加被关联表(publish)的数据,再增加关联表(book)的数据
mysql>: insert into publish(name, address, phone) values
('人民出版社', '北京', '010-110'),
('西交大出版社', '西安', '010-119'),
('老男孩出版社', '上海', '010-120');

mysql>: insert into book(name, price, publish_id) values
('西游记', 6.66, 1),
('东游记', 8.66, 1),
('python从入门到入土', 2.66, 2),
('轮程序员修养之道', 3.66, 3),
('好好活着', 88.88, 3);
# 没有被关联的字段,插入依旧错误
mysql>: insert into book(name, price, publish_id) values ('打脸之道', 0.3, 4);  # 失败


# 更新:直接更新被关联表的(publish) 主键,关联表(book) 外键 会级联更新
mysql>: update publish set id=10 where id=1;
# 更新:直接更新关联表的(book) 外键,修改的值对应被关联表(publish) 主键 如果存在,可以更新成功,反之失败
mysql>: update book set publish_id=2 where id=4;  # 成功
mysql>: update book set publish_id=1 where id=4;  # 失败


# 删:
#   删被关联表,关联表会被级联删除
mysql>: delete from publish where id = 2;

#   删关联表,被关联表不会发生变化
mysql>: delete from book where publish_id = 3;

# 假设:书与作者也是 一对多 关系,一个作者可以出版多本书
create table book(
    id int primary key auto_increment,
    name varchar(64) not null,
    price decimal(5, 2) default 0,
    publish_id int,  # 一对多的外键不能设置唯一
    foreign key(publish_id) references publish(id)
    on update cascade
    on delete cascade
    
    # 建立与作者 一对多 的外键关联
    author_id int,  
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);
上一篇:PAT甲题题解-1106. Lowest Price in Supply Chain (25)-(dfs计算树的最小层数)


下一篇:[转]QT QDateTime类、QTimer类