How to use sequence as default value

From: William Rodriguez <LordWilRod_at_worldnet.att.net>
Date: 13 Oct 1998 05:43:56 GMT
Message-ID: <3622E824.E60A75F2@worldnet.att.net>

In TSQL I could create a table with:

create table books (

     ID int IDENTITY (1, 1) NOT NULL ,
     Name varchar (20) NOT NULL ,
     CONSTRAINT PK_BookID PRIMARY KEY  CLUSTERED (ID) 
                WITH  FILLFACTOR = 40

)

I can't seem to duplicate this simple table under Oracle. Note that the ID column is both a sequence and a primary key.

I tried the following to no avail:

CREATE SEQUENCE BookID

       INCREMENT BY 1
       START WITH 1

create table books (
  ID int NOT NULL CONSTRAINT PK_BookID PRIMARY KEY USING INDEX PCTFREE 60,
  Name varchar2 (20) NOT NULL ,
  CONSTRAINT SEQ_BookID (ID) DEFAULT (BookID.NextVal)
)

/

I tried switching the ordering between the primary key and default constraints and still got nowhere. Removing the primary key constraint and just trying to make the ID column default to the BookID.NextVal also failed.

Is is possible to make a column default to a Sequence.NextVal?

Thanks! Received on Tue Oct 13 1998 - 00:43:56 CDT