Re: Auto Increment Primary key?

In article
<Pine.LNX.4.10.9912171604300.29563-100000_at_alfred.laffeycomputer.com>,   John Shaft <shaft_at_meanmutha.com> wrote:
> Hi,
>
> Does Oracle has a way to define a table such that the primary key gets
> auto-incremented with each new insertion? If not, how do you keep your
> primary keys unique? (With a store value read and incremented in a
> transaction?)
>
> I have a databse devloped orignally for MySQL and want to port it to
> Oracle 8. I wondered about this issue.

Well if you *MUST* use sequential numbers for a primary key, use an Oracle Sequence. Though I would think with a little thought you could redesign your database so that you would be able to use a more logical key.

Anyway here it is:
CREATE SEQUENCE sequence_name
START WITH n
INCREMENT BY i
MINVALUE j
MAXVALUE k
CYCLE | NOCYCLE
CACHE x
ORDER | NOORDER Then the basic select you will use to insert your primary key is: INSERT INTO my_table(primary_key_column, col2, col3) VALUES (my_sequence.nextval, value2, value3);

n, i, j, k, x are integers. You would want NOCYCLE (so Oracle doesn't reuse values once the sequence maxes out) and ORDER | NOORDER refers to doling out sequence values in the order in which the requests are recieved and CACHE will put the specified number of values into memory for faster access.

I would advise against such a scheme though, and urge you to do some database redesign. It is my notion that any table can have a primary key derived from the values of the fields that make up the row.

My proposed solution would be to create a trigger that would concatenate enough columns to pretty much gurantee a unique value and then check that it is unique and if not then slap a number onto the end of the value (e.g. foobar, foobar2, foobar3 etc) and then finally to perform the insert.

Any comments on my ideas from wisened old DBAs or DB developers?

--
From: Mark Framness
http://netnet.net/~farmer/index.html
All standard disclaimers apply anyone who say otherwise is itching for a

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jan 03 2000 - 09:12:28 CST