Oracle SQL missing smth

Question

试图建立这些表，总是有一个错误或遗漏的东西，有人可以帮助我吗?哪里是max_mice，我正在检查它是否在这些值之间，不工作，为什么?不知道出了什么问题，已经到处搜索，不知道为什么不会被创造出来……更新:现在我有表事件的问题…

更新:错误是我有:约束fun_maxmi_ch CHECK (200>Max_mice>= min_mice)而不是下面的代码。

CREATE TABLE Functions (
function VARCHAR(10) CONSTRAINT fun_fu_pk PRIMARY KEY,
min_mice NUMBER(3) CONSTRAINT fun_minmi_ch CHECK (min_mice > 5),
max_mice NUMBER(3),
CONSTRAINT fun_maxmi_ch CHECK (max_mice >= min_mice and max_mice < 200)
); 

但是创建表仍然有问题事件不知道是什么问题!!

CREATE TABLE Incidents (
nickname VARCHAR2(15), 
enemy_name VARCHAR2(15), 
incident_date DATE CONSTRAINT inc_indate_nn NOT NULL,
incident_desc VARCHAR2(50),
CONSTRAINT inc_con_pk PRIMARY KEY (nickname, enemy_name),
CONSTRAINT inc_nic_fk FOREIGN KEY (nickname) REFERENCES Cats(nickname),
CONSTRAINT inc_enname_fk FOREIGN KEY (enemy_name) REFERENCES Enemies(enemy_name),
);

完整代码:

CREATE TABLE Enemies (
enemy_name VARCHAR2(15), 
hostility_degree NUMBER(2) CONSTRAINT hos_degree_ch CHECK (hostility_degree BETWEEN 1 AND 10),
species VARCHAR2(15),
bride VARCHAR2(20),
CONSTRAINT ene_name_pk PRIMARY KEY(enemy_name)
);
CREATE TABLE Functions (
function VARCHAR(10) CONSTRAINT fun_fu_pk PRIMARY KEY,
min_mice NUMBER(3) CONSTRAINT fun_minmi_ch CHECK (min_mice > 5),
max_mice NUMBER(3),
CONSTRAINT fun_maxmi_ch CHECK (max_mice >= min_mice and max_mice < 200)
); 
CREATE TABLE Bands (
Band_no NUMBER(2) CONSTRAINT ban_no_pk PRIMARY KEY,
name VARCHAR2(20) CONSTRAINT ban_name_nn NOT NULL,
site VARCHAR2(15) CONSTRAINT ban_site_un UNIQUE,
band_chief VARCHAR(15) CONSTRAINT ban_chief_un UNIQUE
);

CREATE TABLE Cats (
name VARCHAR2(15) CONSTRAINT cat_name_nn NOT NULL,
gender VARCHAR2(1) CONSTRAINT cat_gen_ch CHECK (gender IN('M', 'W')),
nickname VARCHAR2(15) CONSTRAINT cat_pk PRIMARY KEY,
function VARCHAR2(10), 
chief VARCHAR2(15), 
in_herd_since DATE DEFAULT SYSDATE CONSTRAINT cat_inherd_nn NOT NULL,
mice_ration NUMBER(3),
mice_extra NUMBER(3),
band_no NUMBER(2),
CONSTRAINT cat_banno_fk FOREIGN KEY (band_no) REFERENCES Bands(band_no),
CONSTRAINT cat_chief_fk FOREIGN KEY (chief) REFERENCES Cats(nickname),
CONSTRAINT cat_fun_fk FOREIGN KEY (function) REFERENCES Functions(function)
);
ALTER TABLE Bands 
ADD CONSTRAINT ban_chief_fk FOREIGN KEY (band_chief) REFERENCES Cats(nickname);

CREATE TABLE Incidents (
nickname VARCHAR2(15), 
enemy_name VARCHAR2(15), 
incident_date DATE CONSTRAINT inc_indate_nn NOT NULL,
incident_desc VARCHAR2(50),
CONSTRAINT inc_con_pk PRIMARY KEY (nickname, enemy_name),
CONSTRAINT inc_nic_fk FOREIGN KEY (nickname) REFERENCES Cats(nickname),
CONSTRAINT inc_enname_fk FOREIGN KEY (enemy_name) REFERENCES Enemies(enemy_name),
);

Answer 1

这里是max_mice，我正在检查它是否在这些值之间，不工作，为什么?

当涉及到代码中的第一个表Functions时，问题在于最后一个检查约束的声明:

max_mice NUMBER(3) CONSTRAINT fu_maxmi_ch CHECK (200 > max_mice >= min_mouse)

问题:

• 不支持双不等式条件
• 需要在表级别而不是在列级别声明多列检查约束

如此:

CREATE TABLE Functions (
function VARCHAR(10) CONSTRAINT fu_fu_pk PRIMARY KEY,
min_mice NUMBER(3) CONSTRAINT fu_minmi_ch CHECK (min_mice > 5),
max_mice NUMBER(3),
CONSTRAINT fu_maxmi_ch CHECK (max_mice >= min_mice and max_mice < 200)
); 

注意:我不建议将列命名为function，因为它显然与SQL关键字冲突。