oracle sequence id

[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 ?

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]

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]

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]

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]

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 ]