Managing Unique Values Q: I have a relation where the key is a unique id number. How do I use Oracle to automatically manage the generation of unique numbers for my key? A: Oracle allows you to create a piece of data called a SEQUENCE, which you can name, and then call a function NEXTVAL, that returns the next value and increments the stored value. For example: CREATE TABLE test (a INT PRIMARY KEY, b INT); CREATE SEQUENCE aseq; INSERT INTO test VALUES (aseq.NEXTVAL, 3); INSERT INTO test VALUES (aseq.NEXTVAL, 1); INSERT INTO test VALUES (aseq.NEXTVAL, 7); SELECT * FROM VALUES;returns the output: A B ---------- ---------- 1 3 2 1 3 7You can find out what sequence you've created by querying the user_sequences table: SELECT sequence_name FROM user_sequences;and you can drop sequences just as you would any other schema element: DROP SEQUENCE aseq;http://www-db.stanford.edu/~ullman/fcdb/spr01/project3-faq.html
Q: I have a relation where the key is a unique id number. How do I use Oracle to automatically manage the generation of unique numbers for my key? A: Oracle allows you to create a piece of data called a SEQUENCE, which you can name, and then call a function NEXTVAL, that returns the next value and increments the stored value. For example: CREATE TABLE test (a INT PRIMARY KEY, b INT);
CREATE SEQUENCE aseq;
INSERT INTO test VALUES (aseq.NEXTVAL, 3);
INSERT INTO test VALUES (aseq.NEXTVAL, 1);
INSERT INTO test VALUES (aseq.NEXTVAL, 7);
SELECT * FROM VALUES;returns the output:
A B
---------- ----------
1 3
2 1
3 7You can find out what sequence you've created by querying the user_sequences table:
SELECT sequence_name FROM user_sequences;and you can drop sequences just as you would any other schema element:
DROP SEQUENCE aseq;http://www-db.stanford.edu/~ullman/fcdb/spr01/project3-faq.html