Re: What's wrong with this SQL?

ohaya 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
> );
>
> DROP TABLE ss_sessionvar5 CASCADE CONSTRAINTS;
>
> CREATE TABLE ss_sessionvar5 (
> sessionid VARCHAR2(64) NOT NULL,
> varname VARCHAR2(64) NOT NULL,
> varvalue VARCHAR2(4000) NOT NULL,
> PRIMARY KEY (sessionid, varname),
> CONSTRAINT fk_sessionid
> FOREIGN KEY (sessionid)
> REFERENCES ss_sessionspec5(sessionid) ON DELETE CASCADE
> );
>
> CREATE INDEX XIE1ss_sessionvar5 ON ss_sessionvar5
> (
> sessionid
> );
>
> The error is an ORA-01408 and it displays "sessionid", and it looks like
> it's occurring on the 1st "CREATE INDEX" in the above snippet.
>
> Can anyone tell me what's the problem, and how to fix it?
>
> Thanks,
> Jim

I greatly dislike in-line constraint creation which is what you are doing and would suggest you just build the table and then use ALTER TABLE to create your primary key and other constraints. There is no need to be building an index that already exists.

For one thing your primary key should probably bet PCTFREE 0 so as not to read mostly vacuum.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)