【2020python練習】表的關係

@2020.5.5

練習：賬號信息表，用戶組，主機表，主機組

#用戶表
create table user(
id int not null unique auto_increment,
username varchar(20) not null,
password varchar(50) not null,
primary key(username,password)
);

#用戶組表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20) not null unique
);

#主機表
create table host(
id int primary key auto_increment,
ip char(15) not null unique default '127.0.0.1'
);

#業務線表
create table business(
id int primary key auto_increment,
business varchar(20) not null unique
);

#建關係：user與usergroup

create table user2usergroup(
id int not null unique auto_increment,
user_id int not null,
group_id int not null,
primary key(user_id,group_id),
foreign key(user_id) references user(id),
foreign key(group_id) references usergroup(id)
);

#建關係：host與business
create table host2business(
id int not null unique auto_increment,
host_id int not null,
business_id int not null,
primary key(host_id,business_id),
foreign key(host_id) references host(id),
foreign key(business_id) references business(id)
);

#建關係：user與host
create table user2host(
id int not null unique auto_increment,
user_id int not null,
host_id int not null,
primary key(user_id,host_id),
foreign key(user_id) references user(id),
foreign key(host_id) references host(id)
);