Oracle auto increment - newbie so go easy please

Question

Hi all I am looking to create an auto increment in the following table using GUEST_ID as the column that would increment.

CREATE TABLE "HOTEL_BOOKINGS"."GUEST" (
    "GUEST_ID" NUMBER, 
    "LASTNAME" VARCHAR2(100 BYTE),
    "FIRSTNAME" VARCHAR2(100 BYTE), 
    "ADDRESS" VARCHAR2(255 BYTE),
    "TOWN" VARCHAR2(100 BYTE),
    "PHONE" NUMBER,
    "POSTCODE" VARCHAR2(10 BYTE),
    "EMAIL" VARCHAR2(255 BYTE)  
);

I have tried nearly everything to get this working and am at my wits end once again sorry if this is a newbie question but I need coursework handed in tomorrow

Which Oracle version are you using? In Oracle 12 you can simply define a default value taken from a sequence. — , Nov 07 '13 at 20:40
I am using the latest oracle doanloaded around a week ago from the website — user2966477, Nov 07 '13 at 20:46

score 0 · Accepted Answer · answered Nov 07 '13 at 20:34

0

Oracle doesn't have an autoincrement property, but you could use a sequence and an ON INSERT trigger to utilize its value:

CREATE SEQUENCE hotel_bookings_seq 
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

CREATE TRIGGER hotel_bookings_tr
BEFORE INSERT ON hotel_bookings
FOR EACH ROW
BEGIN
    SELECT hotel_bookings_seq.nextval INTO :new.guest_id FROM dual;
END;
/

answered Nov 07 '13 at 20:34

Mureinik

297,002
52
306
350

Many thanks I will give this a try, I think I have tried omething similar but you look like you have extra rows of code in there that I did not have – user2966477 Nov 07 '13 at 20:37
Is there a way to delete already implemented sequences ? – user2966477 Nov 07 '13 at 20:41
@user2966477 If you don't need them, just drop them `DROP SEQUENCE some_sequence` – Mureinik Nov 07 '13 at 20:48
With tis code is it still necessary to add values like this INSERT INTO GUEST(GUEST_ID,FIRSTNAME, LASTNAME, PHONE, ADDRESS, TOWN, POSTCODE, EMAIL)VALUES(seq_person.nextval,'Elroy','Craddock','01497 3139773','36 Yaffingale Gate','Tadley','RG78 2AB','e.craddock@yautia.co.uk'); or can I elimante the VALUE (seq_person.nextval) – user2966477 Nov 07 '13 at 20:54
When running it (I think I have it working) and inputing values, I now get this error SQL Error: ORA-01722: invalid number, but it does not indicate where about ? – user2966477 Nov 07 '13 at 21:08
I think I have some resemblece of success, but hd to change one of my columns to varchar2, many many thaks for the help – user2966477 Nov 07 '13 at 21:12
@user2966477. If you are using one of the latest versions you can just use the [Identity column](http://docs.oracle.com/cd/E16655_01/gateways.121/e22508/migr_tools_feat.htm#DRDAA109) – Filipe Silva Nov 07 '13 at 21:13

Oracle auto increment - newbie so go easy please

1 Answers1