Autonumbering for a primary key

Create a column with auto-numbering to be used as a primary key.

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 tdichiara@techtarget.com 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

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close