0

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);

The error report is as follows:

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.

Any thoughts?

Running Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Community
  • 1
  • 1
sedeh
  • 7,083
  • 6
  • 48
  • 65
  • What is col in the CASE statement? Oracle doesn't identify that and reports an error while creating index. `create unique index only_one_yes on team(isTeamLead);` will work just fine. – zedfoxus May 07 '15 at 03:54

1 Answers1

1

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);

See SQLFiddle.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Ok. If I include project column in the table and want to have one team lead per project, it looks like using index would not work. You can test the [code here](http://ideone.com/ApF2sU) on SQLFiddle. I am wondering if I can have only one `Yes` per project but as many `null` as possible. Thanks. – sedeh May 07 '15 at 13:14
  • See if [this](http://sqlfiddle.com/#!4/528ca8) helps. You could add a trigger to the table to create unique "non 'Yes'" values using the row number or id column etc when the provided insert value is null. – Bohemian May 07 '15 at 14:58