This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes oracle sequence id Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "oracle sequence id" Watch "oracle sequence id" New topic
Author

oracle sequence id

Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
originally posted it in a wrong place, just found this forum --

In many document, it is recommeded to use unique system generated id as the PK for a table, for example

CD (id, author, title)
Track (id, cd_id, name, length)

"Track" tables contains a"cd_id" to refer to "CD" table. As a newbie, I have a few questions, please bear with me --


1. Create table. Suppose I use Oracle, when I create tables, besides theses two tables, do I have to manually create any other table in order to make these "id" be generated ?

2. Insert data -- Supose tables are created, if I want to do a regular insert of a row for Track, when I write my SQL, how do I know what's the "id" and "cd_id" ? Normally I just do "INSERT INTO TRACK VALUES("Gone with wind", 200)", but will this still work in this scenario ??

3. REgular SQL query, as an application user I have no idea about the value of "id" and "cd_id" for rows in the tables, so does the regular query
"SELECT * from CD, TRACK where CD.id = TRACK.cd_id and CD.arthor = "Steve""
still work (i.e. still query by regular attributes and just use the unique id for joining) ?

Thanks.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Raj Ohadi:
originally posted it in a wrong place, just found this forum --

In many document, it is recommeded to use unique system generated id as the PK for a table, for example

CD (id, author, title)
Track (id, cd_id, name, length)

"Track" tables contains a"cd_id" to refer to "CD" table. As a newbie, I have a few questions, please bear with me --


1. Create table. Suppose I use Oracle, when I create tables, besides theses two tables, do I have to manually create any other table in order to make these "id" be generated ?


You would have to create an auto-number object called a sequence.

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

or optionally NOCACHE instead of CACHE value;

Originally posted by Raj Ohadi:
2. Insert data -- Supose tables are created, if I want to do a regular insert of a row for Track, when I write my SQL, how do I know what's the "id" and "cd_id" ? Normally I just do "INSERT INTO TRACK VALUES("Gone with wind", 200)", but will this still work in this scenario ??


you use yourseq.nextval to return your next value for the sequence.
you can utilize this in a couple of ways. The less elegant, INSERT INTO TRACK (id, track, cd_id) Values(track_seq.nextval,"Gone with the wind", 200)

The preferable way is to create a trigger that produces the value for you when a row is inserted without a key specified.

-- we the table creates the key when it is not supplied

CREATE OR REPLACE TRIGGER track_id_trg
BEFORE INSERT ON track
FOR EACH ROW
WHEN (track.id IS NULL)
BEGIN
SELECT track_seq.NEXTVAL
INTO :track.id
FROM dual;
END;
/

INSERT INTO track (track, cd_id)
Values ("Gone with the wind", 200)

This would produce the same type a row as the first example.

Originally posted by Raj Ohadi:
3. REgular SQL query, as an application user I have no idea about the value of "id" and "cd_id" for rows in the tables, so does the regular query
"SELECT * from CD, TRACK where CD.id = TRACK.cd_id and CD.arthor = "Steve""
still work (i.e. still query by regular attributes and just use the unique id for joining) ?

Thanks.


Yes and you would benefit from using keys for joins (more efficient).

It would return the ID, CD_ID, author where author = "Steve".

[ October 09, 2007: Message edited by: Paul Campbell ]
[ October 09, 2007: Message edited by: Paul Campbell ]
Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
Paul,

bear with me for one more stupid question -- TRACK table includes "cd_id", when I actually insert a row to TRACK, what I know is "name" and "length", and the "author" who publishes the CD, but I do NOT know that "cd_id" assciated with that "author". So when I insert into TRACK table how do I handle the value of "cd_id", should I first "select cd_id from CD where author = 'steve'", then use this value for "cd_id" when I do insert for TRACK ? Sounds not convenient, any good way to handle it ?

Thanks.
Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
Paul,

bear with me for one more stupid question -- TRACK table includes "cd_id", when I actually insert a row to TRACK, what I know is "name" and "length", and the "author" who publishes the CD, but I do NOT know that "cd_id" assciated with that "author". So when I insert into TRACK table how do I handle the value of "cd_id", should I first "select cd_id from CD where author = 'steve'", then use this value for "cd_id" when I do insert for TRACK ? Sounds not convenient, any good way to handle it ?

Thanks.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Raj Ohadi:
Paul,

bear with me for one more stupid question -- TRACK table includes "cd_id", when I actually insert a row to TRACK, what I know is "name" and "length", and the "author" who publishes the CD, but I do NOT know that "cd_id" assciated with that "author". So when I insert into TRACK table how do I handle the value of "cd_id", should I first "select cd_id from CD where author = 'steve'", then use this value for "cd_id" when I do insert for TRACK ? Sounds not convenient, any good way to handle it ?

Thanks.



I know that I have quite a number of CDs where the same artist has the same track, but on different album releases... but ignoring that and assuming you just have to do it in this very unique way.

If your table looks like something this

tbl_tracks
track number NOT NULL;
cd_id number;
author varchar2;
name varchar2;
track varchar2;
track_length number;

/* this SQL assumes the author only has a single cd_id and implies you would have a unique key constraint for author */

Insert tbl_tracks (cd_id, author, name, track, track_length)
Select cd_id, authorIn, nameIn, trackIn. lenghIn
from tracks
Where author = authorIn
[ October 10, 2007: Message edited by: Paul Campbell ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: oracle sequence id
 
Similar Threads
Hibernate: persist an object containing a list with more objects
Why? uniqueConstraints is not working!
cascading classes and tables (how to update ?)
TableGenerator
oracle sequence id and table data insertion