How do you create a column with auto-numbering that can be used as a primary key? This is a very simple and probably very common problem. Here is a simple and universal procedure to do this.
1. Create a column in the table with the number datatype; this will be used to hold the auto-generated numbers.
2. Create a sequence that will keep track of the auto number value.
3. Create a trigger that will insert the next value of your auto number into the newly created column every time the new insert occurs.
4. Replace schima_name, sequence_name, trigger_name, table_name, column_name, with appropriate values, like this:
CREATE SEQUENCE schima_name.sequence_name INCREMENT BY 1 NOMAXVALUE MINVALUE 1 NOCYCLE CACHE 20 NOORDER; CREATE OR REPLACE TRIGGER schima_name.trigger_name BEFORE INSERT ON schima_name.table_name REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN SELECT sequence_name.NEXTVAL INTO :NEW.column_name FROM DUAL; END;
For More Information
- What do you think about this tip? E-mail the Editor at
- firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in January 2002