Oracle中不区分大小写的主键

Question

我们数据的语义是不区分大小写的，因此我们配置了Oracle会话来支持不区分大小写：

alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_AI;

为了充分利用索引，我们还希望主键不区分大小写：

create table SCHEMA_PROPERTY (
  NAME  nvarchar2(64)   not null,
  VALUE nvarchar2(1024),
  constraint SP_PK primary key (nlssort(NAME))
)

然而，这会遇到“ORA-00904: : invalid identifier”错误，因此我认为在PK定义中使用nlssort()函数是不可能的。 接下来尝试将大小写不敏感的唯一索引与主键关联：

create table SCHEMA_PROPERTY (
  NAME  nvarchar2(64) primary key using index (
      create unique index SP_UQ on SCHEMA_PROPERTY(nlssort(NAME))),
  VALUE nvarchar2(1024)
);

但这次也失败了：

Error: ORA-14196: Specified index cannot be used to enforce the constraint.
14196. 00000 -  "Specified index cannot be used to enforce the constraint."
*Cause:    The index specified to enforce the constraint is unsuitable
           for the purpose.
*Action:   Specify a suitable index or allow one to be built automatically.

我应该得出结论，Oracle不支持主键约束的大小写不敏感语义吗？这在MSSQL中可以正常工作，因为它在处理排序规则时有一个更简单的方法。

当然，我们可以创建一个唯一索引代替主键，但我想先确定正常的操作方式是否受到支持。

我们使用的Oracle版本是11.2.0.1。

- Bogdan Calmac

Answer 1

如果您使用的是11.2版本，您可以使用虚拟列来实现此目的：

CREATE TABLE SCHEMA_PROPERTY (
   REAL_NAME  nvarchar2(64) not null,
   NAME       generated always as (lower(real_name)) primary key,
   VALUE nvarchar2(1024)
);

- user330315

---

lower()和nlssort()之间没有区别。我发帖的重点是似乎不可能基于函数定义PK。PK只能基于实际列。 - Bogdan Calmac

@BogdanCalmac：你是对的，抱歉在发布之前应该测试一下。使用11.x版本有一个解决方法，请查看我的编辑。 - user330315

我们必须支持Oracle 10g，但即使没有它，使用唯一索引看起来比使用主键更丑陋。 - Bogdan Calmac

@BogdanCalmac：如果你可以使用唯一索引，那么请务必使用它！我认为你需要PK定义才能创建指向该表的外键。 - user330315

Answer 2

创建一个唯一索引以强制执行不区分大小写的主键：

create table SCHEMA_PROPERTY (
  NAME  nvarchar2(64),
  VALUE nvarchar2(1024),
  constraint SP_PK primary key (NAME)
);

create unique index SP_UN on SCHEMA_PROPERTY(lower(NAME));

- fnds