如果给定条件为true,是否有方法忽略唯一约束?
例如,我在数据库中有3列,它们形成了一个唯一的约束:
create table example_table
(
column_primarykey RAW(16) default NULL not null,
column_a number(8) not null,
column_b number(8) not null,
column_c number(8) not null,
constraint constraint_1
unique(column_a, column_b, column_c)
constraint constraint_2
primary key (column_primarykey)
现在我添加第四列:
alter table example_table
add column_d number(8) not null,
我想要实现的是,如果表中已经存在column_d的值,则会忽略唯一约束。如果column_d在表中不是唯一的,则会忽略唯一约束,您可以将行添加到表中。例如,这是我的表中的现有数据(忽略不相关的主要关键原因(:
column_a | column_c | column-d|||
---|---|---|---|---|
1 | 2 | 3 | <1>||
3 | 4 | 5 | 2 |
听起来好像您试图将两个或多个表压缩为一个表。
- 没有更多背景很难说
例如,如果你制作了一个大的平面文件,你可能会有这个?
a | b | c | >dx | yz||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 2 | 3 | 1 | 1 | 3 | 1 | |||||
1 | 2 | 3 | 12 | 8 | 7|||||||
1 | 2 | 3 | 1 | 5 | 92 | ||||||
4 | 5 | 6 | 2 | 9 | 8 | 7 | |||||
4 | 5 | 6 | 24 | 5 | 6 | ||||||
4 | 5 | 6 | 2 | 3 | 2 | 1 | |||||
4 | 5 | 6 | 2 | 2 | 1 | 0 | |||||
据我所知,您有两个唯一的约束(在A,B,C
和D
上(,并且如果在A,B,C,D
上有重复,您希望同时抑制这两个约束。
这是AFAIK不可能在一个表中完成的,所以您必须将设置拆分为两个表。
第一个ABCD
检查约束,但不允许重复,第二个TAB
存储实际数据并引用第一个表。
在插入触发器之前的中,A,B,C,D
上的唯一值被合并到第一个表中。
create table abcd
(a int,
b int,
c int,
d int,
constraint abcd unique (a,b,c,d),
constraint abc unique (a,b,c),
constraint d unique(d));
drop table tab;
create table tab
(pk int,
a int,
b int,
c int,
d int,
primary key(pk),
foreign key(a,b,c,d) references abcd(a,b,c,d)
);
CREATE OR REPLACE TRIGGER tab_trigger
BEFORE INSERT ON tab
FOR EACH ROW
BEGIN
merge into abcd using
(select :new.a a, :new.b b, :new.c c, :new.d d from dual) src
on (abcd.a = src.a and abcd.b = src.b and abcd.c = src.c and abcd.d = src.d)
when not matched then insert (a,b,c,d) values (src.a, src.b, src.c, src.d)
;
END;
/
按预期测试运行
insert into tab(PK, A, B, C, D) values (1, 1,2,3,1);
1 row inserted.
insert into tab(PK, A, B, C, D) values (2, 3,4,5,2);
1 row inserted.
insert into tab(PK, A, B, C, D) values (3, 1,2,3,1);
1 row inserted.
insert into tab(PK, A, B, C, D) values (4, 1,2,3,4);
ORA-00001: unique constraint (ZZZ.ABC) violated
insert into tab(PK, A, B, C, D) values (5, 5,6,7,1);
ORA-00001: unique constraint (ZZZ.D) violated
insert into tab(PK, A, B, C, D) values (6, 3,4,5,2);
1 row inserted.
insert into tab(PK, A, B, C, D) values (7, 7,8,9,3);
1 row inserted.
无论如何,我必须承认我不喜欢基于触发器的解决方案,我更喜欢的是带有检查视图的延迟验证。
也就是说,你可以插入任何行,但在视图中你可以看到哪些行是无效的,你能处理它吗?
验证查询相当直接,它使用分析函数来获得键的重复计数。
with abcd as (
select PK, A, B, C, D,
count(*) over (partition by A, B, C order by PK) cnt_abc,
count(*) over (partition by D order by PK) cnt_d,
count(*) over (partition by A, B, C, D order by PK) cnt_abcd
from tab)
select
PK, A, B, C, D, CNT_ABC, CNT_D, CNT_ABCD,
case when (CNT_ABC > 1 or CNT_D > 1) and CNT_ABCD = 1 then 'rejected'
else 'accepted' end as status
from abcd
order by PK;
ID A B C D CNT_ABC CNT_D CNT_ABCD STATUS
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
1 1 2 3 1 1 1 1 accepted
2 3 4 5 2 1 1 1 accepted
3 1 2 3 1 2 2 2 accepted
4 1 2 3 4 3 1 1 rejected
5 5 6 7 1 1 3 1 rejected
6 3 4 5 2 2 2 2 accepted
7 7 8 9 3 1 1 1 accepted