mysql 外键_MySQL外键实例

外键实例

性别表

create table gender(
    gid int auto_increment primary key not null,
    gender char(10)); 
insert into gender(gender) values 
    ('male'),
    ('female'), 
    ('unknown');

# 一个表只能有一个主键， 但是主键可以是由不为空唯一表示的两列（多列）构成。

部门表

create table department(
    did int auto_increment primary key not null,
    department char(20)); 
insert into department(department) values 
    ('HR'), 
    ('IT'), 
    ('Accounting'), 
    ('Marketing'), 
    ('Security');

员工表(一对多，一个部门可以对应多个员工，性别也是)

create table staff(
    sid int auto_increment primary key not null, 
    name char(20), 
    department_id int, 
    gender_id int, 
    constraint fk_staff_department_id FOREIGN KEY (department_id) REFERENCES department(did),
    constraint fk_staff_gender_id foreign key (gender_id) references gender(gid));

查看表结构

desc staff;

查看外键表

show create table staff;

插入员工

insert into staff(name, department_id, gender_id) values 
('Alex', 1, 1), 
('Ana', 2, 2), 
('Bob', 3, 1), 
('Cathy', 2, 2), 
('Deja', 5,3), 
('Frank', 1, 3), 
('Helen', 4, 2), 
('Ira', 5, 1), 
('Zoe', 3, 2);

工作站

create table workstation(
    wid int auto_increment not null, 
    ws_name char(10), 
    primary key(wid));
insert into workstation(ws_name) values (ws1), (ws2), (ws3), (ws4), (ws5));

员工和工作站对应表（多对多，一个员工可以用多个工作站，一个工作站可以别多个员工使用）

create table staff_workstation(
    sw_id int auto_increment not null, 
    staff_id int, 
    workstation_id int, 
    primary key (sw_id),
    unique uq_sid_wid (staff_id, workstation_id),  #  表示联合唯一索引
    constraint fk_staff_workstation_staff_id foreign key (staff_id) references staff(sid), 
    constraint fk_staff_workstation_id foreign key (workstation_id) references workstation(wid));

# int类型，如果插入的是浮点数，会被四舍五入，例如插入1.5，插入后会自动变成2。

子表中所外键字段的数据类型必须与父表中所参考的字段的数据类型一致