限制数据库表，以便只有一行可以在列中具有特定值 [英] Constraining a database table so only one row can have a particular value in a column

问题描述

我是SQL的新手，当我尝试遵循帖子

I am brand new to SQL and I am trying to figure out an error message I get when I try to follow the suggestions in the post here to enforce only one 'Yes' in my column.

DROP TABLE team  CASCADE CONSTRAINTS PURGE;
create table team (
  name varchar2(4) NOT NULL UNIQUE,
  isTeamLead char(3) 
    check (isTeamLead in ('Yes')));

create unique index only_one_yes on team(isTeamLead)
(case when col='YES' then 'YES' end);

错误报告如下:

Error report -
SQL Error: ORA-02158: invalid CREATE INDEX option
02158. 00000 -  "invalid CREATE INDEX option"
*Cause:    An option other than COMPRESS, NOCOMPRESS, PCTFREE, INITRANS,
           MAXTRANS, STORAGE, TABLESPACE, PARALLEL, NOPARALLEL, RECOVERABLE,
           UNRECOVERABLE, LOGGING, NOLOGGING, LOCAL, or GLOBAL was specified.
*Action:   Choose one of the valid CREATE INDEX options.

有什么想法吗?

运行Oracle Database 11g企业版11.2.0.1.0

推荐答案

删除create index语句的case部分.这样执行就可以了:

Remove the case part of the create index statement. This executes OK:

create table team (
  name varchar2(4) NOT NULL UNIQUE,
  isTeamLead char(3) check (isTeamLead in ('Yes'))
);

create unique index only_one_yes on team(isTeamLead);

insert into team values ('x', 'Yes');
insert into team values ('y', null);

请参见 SQLFiddle .

这篇关于限制数据库表，以便只有一行可以在列中具有特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！