| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to use sequence as default value
On 13 Oct 1998 05:43:56 GMT, William Rodriguez
<LordWilRod_at_worldnet.att.net> wrote:
>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)
>)
>/
Remote your CONSTRAINT clause on your CREATE TABLE statement, and create a trigger by executing:
CREATE OR REPLACE TRIGGER t_i_books BEFORE INSERT ON books FOR EACH
ROW
DECLARE
id NUMBER;
BEGIN
SELECT foo_id_seq.NEXTVAL INTO id FROM DUAL;
:new.id := id;
END;
/
>
>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!
P.S. This question is answered many times in this newsgroup.
DejaNews (http://www.dejanews.com) lets you search on all previous
postings. Chances are, somebody already asked the question (and
received an answer).
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |