Oracle-对可为空字段的唯一约束

Question

关于Oracle中对可为空的列进行唯一约束定义的可能解决方案，我想向你们请教/收集一些想法。

null

+---+-----------+-----------+-----------+
|ID |FIRST_NAME |LAST_NAME  |EXT_CODE   |
+---+-----------+-----------+-----------+
|1  |Peter      |Pletan     |ABC        |
|2  |John       |Dollar     |DEF        |
|3  |Mia        |Zin        |GHI        |
|4  |Jasper     |Blau       |NULL       |
|5  |George     |Khan       |NULL       |
-----------------------------------------

现在困难的部分来了。我决定，在这个表中，可以存储更多（独立）客户的数据。为此，我添加了名为customer_code的新列。这段代码实际上将表分割成不同的空格，而每个客户只能看到自己的数据。

为此，引入了oracle vpd（虚拟专用数据库）。

1. 每个客户都使用自己的oracle用户
2. 登录时，将加载客户代码
3. 将CUSTOMER_CODE=“MY_CODE”的谓词（在步骤2中加载）追加到每个查询

+---------------+---+-----------+-----------+-----------+
|CUSTOMER_CODE  |ID |FIRST_NAME |LAST_NAME  |EXT_CODE   |
+---------------+---+-----------+-----------+-----------+
|C1             |1  |Peter      |Pletan     |ABC        |
|C1             |2  |John       |Dollar     |DEF        |
|C1             |3  |Mia        |Zin        |GHI        |
|C1             |4  |Jasper     |Blau       |NULL       |
|C1             |5  |George     |Khan       |NULL       |
|C2             |6  |Paul       |Walker     |1          |
|C2             |7  |Simon      |Sleeper    |2          |
|C2             |8  |Lian       |Driver     |3          |
|C2             |9  |Cor        |Pilot      |NULL       |
|C2             |10 |Martin     |Oldman     |NULL       |
---------------------------------------------------------

null

+---------------+---+-----------+-----------+-----------+
|CUSTOMER_CODE  |ID |FIRST_NAME |LAST_NAME  |EXT_CODE   |
+---------------+---+-----------+-----------+-----------+
|C1             |1  |Peter      |Pletan     |ABC        |
|C2             |2  |John       |Dollar     |ABC        |

我现在有什么可能？1.基于函数的索引（drop unique constraint）-索引，如果any为null，它会将两个值都设置为null，因此它根本不被索引=>可能是一个解决方案，但是索引不能与unique constraint相反地延迟

触发器-它检查数据并抛出异常（仅当两个值都不为空时）

Make ext_code not null-在组合上放置常规唯一约束(ext_code,customer_code)=>不可行选项

Answer 1

您没有说明使用的是Oracle的哪个版本,但从11g开始,您可以使用具有唯一约束的虚拟列：

alter table customer add (unq_col varchar2(24) -- or necessary size
  generated always as (case when ext_code is null then null
    else customer_code||'~'||ext_code end));
alter table customer add (constraint unq_col_con unique (unq_col));

生成的列可以以您认为安全的任何方式构建--如果您可以标识一个永远不可能在其中一列中的字符，则使用分隔符，或填充，或任何合适的方法。

则尝试在客户内部复制代码将失败：

update customer set ext_code = 'ABC' where ext_code = 'DEF'

Error report -
SQL Error: ORA-00001: unique constraint (SCHEMA.UNQ_COL_CON) violated
00001. 00000 -  "unique constraint (%s.%s) violated"

但对于不同的客户也是可以的：

update customer set ext_code = 'ABC' where ext_code = '1';

1 row updated.