你能帮我重新设计这两个表吗?
时间: 2024-02-18 15:05:39 浏览: 22
当然可以,以下是我为您重新设计的两个表的建表语句和表结构信息:
```
-- BUILD_ADMIN_INFO 表
CREATE TABLE BUILD_ADMIN_INFO (
ID INT PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
SEX CHAR(2) NOT NULL,
PHONE VARCHAR2(20) NOT NULL,
BUILDING_ID INT NOT NULL,
DORM_ID INT NOT NULL,
FOREIGN KEY (BUILDING_ID) REFERENCES BUILDING_INFO (ID),
FOREIGN KEY (DORM_ID) REFERENCES DORM_INFO (ID)
);
-- DORM_INFO 表
CREATE TABLE DORM_INFO (
ID INT PRIMARY KEY,
NUMBER VARCHAR2(10) NOT NULL,
FLOOR VARCHAR2(10) NOT NULL,
BED_COUNT INT NOT NULL,
BUILDING_ID INT NOT NULL,
CHIEF_ID INT NOT NULL,
FOREIGN KEY (BUILDING_ID) REFERENCES BUILDING_INFO (ID),
FOREIGN KEY (CHIEF_ID) REFERENCES BUILD_ADMIN_INFO (ID)
);
```
# 表结构信息
## BUILD_ADMIN_INFO 表
| 列名 | 数据类型 | 长度/精度 | 是否为空 | 主键/外键 |
| --- | --- | --- | --- | --- |
| ID | INT | - | NOT NULL | PRIMARY KEY |
| NAME | VARCHAR2 | 20 | NOT NULL | - |
| SEX | CHAR | 2 | NOT NULL | - |
| PHONE | VARCHAR2 | 20 | NOT NULL | - |
| BUILDING_ID | INT | - | NOT NULL | FOREIGN KEY |
| DORM_ID | INT | - | NOT NULL | FOREIGN KEY |
## DORM_INFO 表
| 列名 | 数据类型 | 长度/精度 | 是否为空 | 主键/外键 |
| --- | --- | --- | --- | --- |
| ID | INT | - | NOT NULL | PRIMARY KEY |
| NUMBER | VARCHAR2 | 10 | NOT NULL | - |
| FLOOR | VARCHAR2 | 10 | NOT NULL | - |
| BED_COUNT | INT | - | NOT NULL | - |
| BUILDING_ID | INT | - | NOT NULL | FOREIGN KEY |
| CHIEF_ID | INT | - | NOT NULL | FOREIGN KEY |
重新设计的表中,将 BUILD_ADMIN_INFO 表的 BUILDING_ID 和 DORM_ID 字段作为外键,分别指向 BUILDING_INFO 表和 DORM_INFO 表的主键。同时,将 DORM_INFO 表的 CHIEF 字段改为 CHIEF_ID,并作为外键指向 BUILD_ADMIN_INFO 表的主键。这样可以保证数据的一致性和正确性,避免了找不到父键的错误。