优化create table inhotel( innum int not null unique auto_increment comment '入住编号', indate date not null comment '客户入住时间', outdate date not null comment '客户应退房时间', cusname varchar(10) not null comment '客户姓名', roomnum int not null comment '房间号', worknum int not null comment '员工', primary key (innum), -- 将入住编号设置为主键 foreign key (cusname) references customer(cusname), foreign key (roomnum) references room(roomnum), foreign key (worknum) references worker(worknum) ) engine=InnoDB;
时间: 2024-02-26 11:54:08 浏览: 61
MySQL 序列 AUTO_INCREMENT详解及实例代码
您可以优化这个create table语句的几个方面:
1. 将innum int not null unique auto_increment comment '入住编号'设置为主键,可以直接在innum后面添加primary key来实现。
2. 您可以使用ON DELETE CASCADE和ON UPDATE CASCADE选项,当外键关联的表的主键更新或被删除时,自动更新或删除关联的行。例如,您可以将foreign key (cusname) references customer(cusname)改为foreign key (cusname) references customer(cusname) ON DELETE CASCADE ON UPDATE CASCADE。
3. 您可以在创建表时为每个列添加注释,这有助于提高代码的可读性和可维护性。
因此,您可以优化后的create table语句如下:
create table inhotel(
innum int not null auto_increment comment '入住编号',
indate date not null comment '客户入住时间',
outdate date not null comment '客户应退房时间',
cusname varchar(10) not null comment '客户姓名',
roomnum int not null comment '房间号',
worknum int not null comment '员工',
primary key (innum),
foreign key (cusname) references customer(cusname) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key (roomnum) references room(roomnum) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key (worknum) references worker(worknum) ON DELETE CASCADE ON UPDATE CASCADE
) engine=InnoDB;
阅读全文