OracleSQL漏掉了

嘗試構建這些表并且總是出現錯誤或丟失某些內容，有人可以幫助我嗎？我在哪里檢查 max_mice 是否介于這些值之間并且為什么不起作用？不知道出了什么問題，已經到處搜索了，不知道為什么它們不會被創建...更新：現在我遇到了有關表事件的問題...

更新：錯誤是我有：CONSTRAINT 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),
);

uj5u.com熱心網友回復：

這是 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)
); 

注意：我不建議為 column 命名function，因為它顯然與 SQL 關鍵字沖突。

標籤：sql甲骨文外键创建表检查约束

上一篇：Oracle函式獲取最小值

下一篇：查找沒有員工的部門