SQL Oracle约束值在多列中唯一

Question

假设我有一个表格，其中有三列——A、B和C。我想确保如果我向列A插入某个值（比如x），我就不能插入一个元组，其中B或C等于x，即对于所有元组，值x应该仅在列A中唯一。

请注意，x可以在A中重复出现，但对于其他的元组，其B或C不能等于x。

我知道SQL中有UNIQUE子句，但它只能确保一个值仅在特定列中出现一次。由于Oracle中的CHECK语句不允许子查询，我无法弄清如何实现这一点。

编辑（添加更多信息）：

主键是Employee_Number，而涉及到的3列分别是LandlineNo、MobileNo和VOIP。因此，假设这是一个条目：

Employee_Number = 1, LandlineNo = x, MobileNo = y, VOIP = z

那么另一个元组的这个条目将不被允许 -

Employee_Number = 2, LandlineNo = a, MobileNo = x, VOIP = c

另一方面，这个是可以的（是的，两个员工可以拥有相同类型和数量的编号）。

Employee_Number = 2, LandlineNo = x, MobileNo = b, VOIP = c

- Noble Six Taniguchi

---

你能否用好行和坏行的例子来扩展你的问题？这个表的结构和主键是什么？ - Marco Baldelli

你是否对这个表格设计感到困扰？你所要求的是确保每个数字都属于某种类型。为什么不创建一个“numbers”表，其中主键是数字（或者是ID加上对数字的唯一约束，如果你更喜欢这个选项），然后有一个类型列。因此，每个数字都是唯一的且具有一个类型。然后在员工表中，你只需要使用三个外键来引用那个数字表（即三个数字或三个ID）。 - Thorsten Kettner

你使用的是哪个版本的Oracle？ - Sebas

同意@ThorstenKettner的观点--这绝对是一个有问题的数据模型。 - David Aldridge

这是一个失败的数据模型。倾向于使用基本数据库约束的简单而更好的建模将产生简单而更好的结果。请听@ThorstenKettner的建议。 - Michael O'Neill

Answer 1

CREATE MATERIALIZED VIEW mv_my
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT DISTINCT
    CASE 
        WHEN t2.Employee_Number IS NOT NULL THEN 1
        WHEN t3.Employee_Number IS NOT NULL THEN 1
        WHEN t4.Employee_Number IS NOT NULL THEN 1
        ELSE 0
    END AS wrong
FROM table t1
    LEFT JOIN table t2 ON t2.MobileNo = t1.LandlineNo AND t2.Employee_Number != t1.Employee_Number
    LEFT JOIN table t3 ON t3.VOIP = t1.LandlineNo AND t3.Employee_Number != t1.Employee_Number
    LEFT JOIN table t4 ON t4.VOIP = t1.MobileNo AND t4.Employee_Number != t1.Employee_Number
/

ALTER TABLE mv_my ADD CHECK(wrong = 0)
/

根据您的Oracle版本，它可能起作用，也可能不起作用（文档）。

- Sebas

Answer 2

create table table1(
   a varchar2(20) not null,
   b varchar2(20) not null,
   c varchar2(20) not null
)
/
create table ctrs (
   val varchar2(20) unique,
   ctr_a int,
   ctr_b int,
   ctr_c int,
   check(ctr_a*ctr_b+ctr_a*ctr_c+ctr_b*ctr_c=0)
)
/
create trigger table1_trg 
before insert or update or delete on table1
for each row
begin
   if deleting then 
      update ctrs set ctr_a = ctr_a - 1 where val = :old.a;
      update ctrs set ctr_b = ctr_b - 1 where val = :old.b;
      update ctrs set ctr_c = ctr_c - 1 where val = :old.c;
   elsif inserting then
      merge into ctrs using (
        select :new.a as x from dual union all
        select :new.b as x from dual union all
        select :new.c as x from dual
      )
      on (val = x)
      when not matched then 
         insert (val, ctr_a, ctr_b, ctr_c) values (x, 0, 0, 0);
      update ctrs set ctr_a = ctr_a + 1 where val = :new.a;
      update ctrs set ctr_b = ctr_b + 1 where val = :new.b;
      update ctrs set ctr_c = ctr_c + 1 where val = :new.c;
   else
      update ctrs set ctr_a = ctr_a - 1 where val = :old.a;
      update ctrs set ctr_b = ctr_b - 1 where val = :old.b;
      update ctrs set ctr_c = ctr_c - 1 where val = :old.c;
      merge into ctrs using (
        select :new.a as x from dual union all
        select :new.b as x from dual union all
        select :new.c as x from dual
      )
      on (val = x)
      when not matched then 
         insert (val, ctr_a, ctr_b, ctr_c) values (x, 0, 0, 0);
      update ctrs set ctr_a = ctr_a + 1 where val = :new.a;
      update ctrs set ctr_b = ctr_b + 1 where val = :new.b;
      update ctrs set ctr_c = ctr_c + 1 where val = :new.c;
   end if;
end;
/

小提琴

- Egor Skriptunoff