Oracle SQL missing smth-CodePudding

Trying to build these tables and there's always an error or missing something, can someone help me? Where's max_mice i'm checking if it's inbetween those values and doesn't work why? Don't know what's wrong, already searched everywhere, don't know why they won't be created... updated: now i got problems on table incidents...

update: the error was that i had: CONSTRAINT fun_maxmi_ch CHECK (200 > max_mice >= min_mice) instead of the code below.

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

BUT still have a problem creating table Incidents don't know what's the problem!!!

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),
);

Here's the full code:

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),
);

CodePudding user response：

here's max_mice i'm checking if it's in between those values and doesn't work why?

When it comes to the first table in your code, Functions, the problem is with the declaration of the last check constraint:

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

Issues:

the double inequality condition is not supported
a multi-column check constraint need to be declared at table level rather than at column level

This works:

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

Note: I would not recommend naming a column function, since it obviously conflicts with a SQL keyword.