Re: What's wrong with this SQL?

On Fri, 14 Jan 2005 01:17:44 -0500, ohaya <ohaya_at_cox.net> wrote:

>Hi,
>
>I'm trying to run an SQL script as part of installing an application,
>and am getting an error on the script. Here's the part of the script
>where the error occurs:
>
>DROP TABLE ss_sessionspec5 CASCADE CONSTRAINTS;
>
>CREATE TABLE ss_sessionspec5 (
> sessionid VARCHAR2(64) NOT NULL,
> expirationtime INTEGER DEFAULT 0 NOT NULL,
> maxidletime INTEGER DEFAULT 0 NOT NULL,
> lasttouch INTEGER DEFAULT 0 NOT NULL,
> status INTEGER DEFAULT 0 NOT NULL,
> sessionblob VARCHAR2(4000) NOT NULL,
> PRIMARY KEY (sessionid)
>);
>
>CREATE INDEX XIE1ss_sessionspec5 ON ss_sessionspec5
>(
> sessionid
>);

>Can anyone tell me what's the problem, and how to fix it?
>
>Thanks,
>Jim

If you read the documentation, you will find that ORA-01408 means that column "SESSIONID" is already indexed.

And it is not possible to create 2 indexes on one column. And has not been possible at least since 8i, and probably way before that. Period.

Why is column "SESSIONID" already indexed, then? Because the "PRIMARY KEY (sessionid)"-part of the "CREATE TABLE"-statement above implicitly creates an (unique) index on column "SESSIONID" in order to maintain the constraint.

So Oracle's behaviour is expected and correct.

Your vendor's saying things like "none of our other customers had problems" I will leave uncommented. Just get your money back while they are able to pay (I wouldn't wait too long if I were you...).

• Kenneth Koenraadt