Suppose I have 3 columns in a table - A, B and C. I want to ensure that if I insert some value (say x) into a column A, I can't insert a tuple that has either B or C equal to x, i.e, value x should remain unique to column A for all tuples.
假设我在表中有3列--A,B和C.我想确保如果我在列A中插入一些值(比如说x),我就不能插入一个B或C等于x的元组,即,对于所有元组,值x对于列A应该是唯一的。
Note that x can be repeated in A for some other tuple.
注意,对于其他元组,可以在A中重复x。
I'm aware of the UNIQUE clause in SQL, but that is only to ensure that a value occurs in a particular column only once. Since CHECK statements in Oracle do not allow subqueries, I can't figure out how to implement this.
我知道SQL中的UNIQUE子句,但这只是为了确保一个值只在特定列中出现一次。由于Oracle中的CHECK语句不允许子查询,我无法弄清楚如何实现它。
EDIT (to add more info)
编辑(添加更多信息)
Primary key is Employee_Number, while the 3 columns in question are LandlineNo, MobileNo and VOIP. Thus suppose this was one entry :
主键是Employee_Number,而有问题的3列是LandlineNo,MobileNo和VOIP。因此,假设这是一个条目:
Employee_Number = 1, LandlineNo = x, MobileNo = y, VOIP = z
Then this entry for another tuple would NOT be allowed -
然后不允许这个另一个元组的条目 -
Employee_Number = 2, LandlineNo = a, MobileNo = x, VOIP = c
On the other hand, this one would be fine (yes, 2 employees can have the same number of the same type)
另一方面,这个很好(是的,2名员工可以拥有相同类型的相同数量)
Employee_Number = 2, LandlineNo = x, MobileNo = b, VOIP = c
2 个解决方案
#1
2
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)
/
It may or may not work depending on your oracle version (doc)
根据您的oracle版本(doc),它可能会也可能不会起作用
#2
1
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;
/
#1
2
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)
/
It may or may not work depending on your oracle version (doc)
根据您的oracle版本(doc),它可能会也可能不会起作用
#2
1
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;
/