gpt4 book ai didi

oracle - 使同一表中 2 列中的值相互唯一

转载 作者:行者123 更新时间:2023-12-04 19:55:05 25 4
gpt4 key购买 nike

我想要一种情况,我想让同一个表中的 2 列中的值唯一。我想建立一个规则,其中任何值都不能再次出现在表的 2 列中的任何一个中。

例如,考虑一张表 mail_address_book (pk_serial_no, address_a, address_b)address_a & address_b是我想建立相互唯一性的两列。

如果有人尝试运行以下插入语句,则应该是:

create table mail_address_book (pk_serial_no number, address_a varchar2(5), address_b  varchar2(5))
insert into mail_address_book(1,'A','B'); --Allow
insert into mail_address_book(2,'B','A'); --Error
insert into mail_address_book(3,'C','A'); --Error
insert into mail_address_book(4,'C','C'); --Error
insert into mail_address_book(5,'C',null); --Allow

最佳答案

如果您想让同一个表中的 2 列的值是唯一的,那么数据模型似乎有问题 - 两个或更多列包含相同类型的信息。也许最好的解决方案是重新设计 DM 并创建单独的表:

create table mail_address_book (serial_no number primary key /* maybe FK to somewhat */)
/
create table mail_address_entries (
serial_no number, addrno number, address varchar2(5) unique,
constraint pk_fk_mail_address_entries primary key(serial_no, addrno),
constraint fk_mail_address_entries foreign key (serial_no) references mail_address_book (serial_no))
/

作为解决方法,您可以将物理表转换为 View ,然后使用此 View 而不是表来处理所有查询和 DML 语句。考虑以下示例:

create table mail_address_entries (
pk_serial_no number, addrno number, address varchar2(5) unique,
constraint pk_mail_address_entries primary key (pk_serial_no, addrno)
)
/
create or replace view mail_address_book as
select a.pk_serial_no, a.address address_a, b.address address_b
from mail_address_entries a
join mail_address_entries b on (
b.pk_serial_no = a.pk_serial_no and a.addrno = 1 and b.addrno = 2
);

create or replace trigger trig_mail_address_book
instead of insert on mail_address_book
begin
if inserting then -- the same for updating, deleting
insert into mail_address_entries values (:new.pk_serial_no, 1, :new.address_a);
insert into mail_address_entries values (:new.pk_serial_no, 2, :new.address_b);
end if;
end;
/

插入测试数据:

create or replace type addrRow force is object (pk_serial_no number, address_a varchar2(5), address_b varchar2(5));  
/
create or replace type addrRows is table of addrRow;
/
exec dbms_errlog.create_error_log (dml_table_name => 'mail_address_book');

declare
testdata addrRows;
begin
testdata := addrRows (
addrRow (1, 'A', 'B'),
addrRow (2, 'B', 'A'),
addrRow (3, 'C', 'A'),
addrRow (4, 'C', 'C'),
addrRow (5, 'C', null),
addrRow (6, null, null),
addrRow (7, 'D', 'E'),
addrRow (8, 'E', 'F')
);
for r in (select * from table (testdata)) loop
begin
insert into mail_address_book values (r.pk_serial_no, r.address_a, r.address_b);
exception when dup_val_on_index then
insert into err$_mail_address_book (pk_serial_no, address_a, address_b, ora_err_mesg$)
values (r.pk_serial_no, r.address_a, r.address_b, 'error');
end;
end loop;
end;
/

结果:

select to_char (pk_serial_no) no, address_a a, address_b b, 'ok' msg 
from mail_address_book
union all
select pk_serial_no, address_a, address_b, ora_err_mesg$ msg
from err$_mail_address_book
order by 1
;

NO A B MSG
----- ----- ----- ----------
1 A B ok
2 B A error
3 C A error
4 C C error
5 C null ok
6 null null ok
7 D E ok
8 E F error

db<>fiddle

关于oracle - 使同一表中 2 列中的值相互唯一,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56706812/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com