sql - 为什么这个 SQL/DDL 违反了错误的完整性约束？

Question

所以它向我展示了第一个插入失败的 IC4，但就 IC2 而言应该很好。IC2 指出“DB guru”应该高于 200——第一个条目是！

例如，您会看到下面插入列表中的第一个条目是：

INSERT INTO Employee VALUES (10, 'Gray', 'DB guru', 240);

但这失败了 IC4 ......为什么？！

如何指定 CONSTRAINT 必须仅适用于某个分类值，就像我在这里尝试的那样？关于 CHECK 的实施方式，我是否遗漏了一个重要概念？我对 SQL 真的很陌生，并试图解决这个问题：

SPOOL ddl.out
SET ECHO ON

--
-- IMPORTANT: use the names IC1, IC2, etc. as given below.
-- --------------------------------------------------------------------
/*The following DROP command is inserted for convenience so that if you need to recompile
your code, it will drop the table (if it already exists).
*/
DROP TABLE Employee CASCADE CONSTRAINTS;
DROP TABLE Dependent CASCADE CONSTRAINTS;
--
CREATE TABLE Employee
(
id INTEGER PRIMARY KEY,
name CHAR(10) NOT NULL,
rank CHAR(10) NOT NULL,
salary INTEGER NOT NULL,
/*
IC1: The rank is one of: 'DB guru', 'DB expert', or 'DB rookie'
*/
CONSTRAINT IC1 CHECK (rank IN ('DB guru', 'DB expert', 'DB rookie')),
/*
IC2: The salary of a 'DB guru' is above 200.
*/
CONSTRAINT IC2 CHECK (rank != 'DB guru' OR salary > 200),
/*
IC3: The salary of a 'DB expert' is between 80 and 220 (inclusive).
*/
CONSTRAINT IC3 CHECK (rank != 'DB expert' OR (salary >= 80 AND salary <= 220 )),
/*
IC4: The salary of a 'DB rookie' is less than 100.
*/
CONSTRAINT IC4 CHECK (rank != 'DB rookie' OR salary < 100)
);
--
--
CREATE TABLE Dependent
(
empID INTEGER,
dependentName CHAR(20) NOT NULL,
relationship CHAR(20) NOT NULL,
PRIMARY KEY (empID, dependentName),
/*
IC5: empID must refer to an employee in the company. Also:
if an employee is deleted then his/her dependents must be deleted.
IMPORTANT: DO NOT declare this IC as DEFERRABLE.
*/
CONSTRAINT IC5 FOREIGN KEY (empID) REFERENCES Employee(id)
          ON DELETE CASCADE
);
--
-- ----------------------------------------------------------------
-- TESTING THE SCHEMA
-- ----------------------------------------------------------------
INSERT INTO Employee VALUES (10, 'Gray', 'DB guru', 240);
INSERT INTO Employee VALUES (20, 'Garland', 'DB guru', 190);
INSERT INTO Employee VALUES (30, 'Edison', 'DB expert', 210);
INSERT INTO Employee VALUES (40, 'Eckerd', 'DB expert', 70);
INSERT INTO Employee VALUES (50, 'Roberts', 'DB rookie', 110);
INSERT INTO Employee VALUES (60, 'Rush', 'DB rookie', 90);
SELECT * from Employee;
-- ----------------------------------------------------------------
INSERT INTO Dependent VALUES (10, 'Grace', 'daughter');
INSERT INTO Dependent VALUES (10, 'George', 'son');
INSERT INTO Dependent VALUES (60, 'Reba', 'daughter');
INSERT INTO Dependent VALUES (15, 'Dustin', 'son');
SELECT * FROM Dependent;
--
DELETE FROM Employee WHERE id = 10;
SELECT * From Employee;
SELECT * FROM Dependent;
--
SET ECHO OFF
SPOOL OFF

编辑：根据下面选择的正确答案显示工作解决方案。

标签： sqloracleddl

Answer 1

我怀疑您是否可以像这样使约束相互排斥。这可能会帮助您：

CHECK ((rank = 'DB guru' AND salary > 200) or
       (rank = 'DB expert' AND salary >= 80 AND salary <=220 ) or
        ....

编辑：另外，您的 IC4 CHECK CONSTRAINT 逻辑是错误的，应该是：

/*
IC4: The salary of a 'DB rookie' is less than 100.
*/
CONSTRAINT IC4 CHECK (rank = 'DB rookie' AND salary < 100)
);

代替：

/*
IC4: The salary of a 'DB rookie' is less than 100.
*/
CONSTRAINT IC4 CHECK (rank = 'DB rookie' AND salary >= 100)
);

这使得合并条件为：

CHECK ((rank = 'DB guru' AND salary > 200) or
       (rank = 'DB expert' AND salary >= 80 AND salary <=220 ) or
       (rank = 'DB rookie' AND salary < 100))