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 ]