Re: Automatic primary key in Oracle

vvaataja_at_news.abo.fi (Veikko V{{t{j{ TKKK) wrote:

> How to use the oracle sequence feature? I want to use it for an auto
>increment primary key field but I can't figure out how to do it. If I'm
>doing something totally wrong, tell me! This is what I do:
 

> First I create the sequence with CREATE SEQUENCE SEQ_PRKEY;. Then I
>create a table that uses that key with CREATE TABLE SOMETABLE (ID INTEGER
>NOT NULL, SOMEFIELD VARCHAR(10),...,PRIMARY KEY(ID)); Oracle manuals say
>clearly that I can't use sequences in default values, so I figure that I need
>a trigger to put the sequence number in a record records primary key field.
>But how do I get the sequence number in a PL/SQL trigger??? Oracle doesn't
>accept NEW.ID:=SEQ_PRKEY.NEXTVAL; in the trigger body. WHY?
 

> Only thing that I want to do is to have an totally automatic primary key
>field. In interbase it works just like I describe above. How do I do it in
>Oracle???
 

> Thanks!
 

> Veikko Vaataja
> veikko.vaataja_at_abo.fi
>

Heres an example:

rem test triggers for primary key inserts

drop table emp cascade constraints;
drop sequence emp_seq;

create table emp

	(	id number(12,0) not null , 
		lname varchar2(20),
		fname varchar2(20),
		constraint jemp_pk primary key (id)  
			using index pctfree 5 tablespace jared 
	)

create sequence emp_seq

        minvalue 1000 start with 1000 order
/

REM this example shows how to create a trigger to create a primary REM key for a table
REM
REM cannot use sequence directly; must store to a variable REM and then assign to column

CREATE or replace trigger emp_pk_trigger

	before insert on emp
	for each row		-- row level trigger needed to use ":new"
DECLARE
	NewID integer;
	KeyExists exception; -- don't allow user to insert PK

	if :new.id IS NOT NULL then
	 	raise KeyExists;
	end if;

	select emp_seq.nextval into NewId from dual;
	:new.id := NewId ;

EXCEPTION
	WHEN KeyExists THEN
		raise_application_error(-20000,'Primary Keys are generated for EMP

Jared Still, Oracle DBA
RxNet, Division of Value Health
"All opinions are mine, not my employers" jared_at_valleynet.com Received on Tue Jan 16 1996 - 00:00:00 CET