From: "Daniel A. Morgan" <dmorgan@exesolutions.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Altering a table by adding a primary key column
Date: Tue, 05 Jun 2001 23:13:05 -0700
Organization: EXE
Message-ID: <3B1DC9F0.D89DA79D@exesolutions.com>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
References: <thqhcjfoaoqp72@corp.supernews.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Cache-Post-Path: yabetcha.drizzle.com!unknown@ava25.drizzle.com
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
X-Complaints-To: newsabuse@supernews.com
Lines: 30


Bill Hutchison wrote:

> I haven't been able to find documentation on adding a column which is to
> be the primary key of the table.
>
> I want to add a column ID which will be numeric, and should auto-increment.
> I need to populate it for existing records.  Is there a datatype which auto-
> increments, or must this be taken care of by a trigger?
>
> Please suggest how I should accomplish this.  I have EZSQL, but am a
> beginner in using it.
>
> Thanks very much!
> Bill Hutchison

Thankfully, Oracle does not supply autoincrementing fields. In Oracle you create
an object called a SEQUENCE and then use it in an insert statement. Adding the
column is just generic ... alter table ... and adding a column.

To use a sequence in your insert statement you can use the following syntax:

INSERT INTO xyz
(pk_field)
VALUES
(mysequence.nextval);

where "mysequence" is the name of the sequence created.

Daniel A. Morgan