ORACLE SQL AUTO INCREMENT PRIMARY KEY?

Question

Does anyone know how to have an auto increment primary key with the first character defined?
I want an automated primary key but i want the first character to be a letter followed by a 3 digit automatically incremented number e.g C001, C002, C003.

I can only manage to get the auto increment to wok and not have a pre defined character.

Please help!

Answer 1

You'll have to create an insert trigger which will build your key from the letter and probably a sequence.

Have a look at http://www.orafaq.com/wiki/Sequence for how to create sequences ( if you don't already know ).

As for the trigger, you'll probably want to do something like

CREATE OR REPLACE TRIGGER tablename_generatekey

BEFORE INSERT ON tablename

FOR EACH ROW

DECLARE

seq number;

BEGIN

select sequence_name.nextval

into seq

from dual;

:new.primary_key := 'C' || seq;

END;

/

The idea being - when inserting a row - execute this code. The code retrieves the next value from a sequence and then uses that to generate the new value of the primary key ( :new refers to the record going to the database, :old is the record passed in ).

Sorry - I haven't had chance to check if the syntax is correct - but I hope you get the idea.

( More info on triggers at http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#ADFNS012 ).

Answer 2

I would love to know the logic or circumstance into why you think you need to use a string as a primary key? You haven't quite explained what your doing but it's not a good idea. Asuming that your going to build a relation to this primary key as a foreign key in another table and want to index this it's going to use much more room than an integer, and take longer to query than an integer too. If the DB is going to be big and you want it fast. Don't use the string.



Sugestion: Keep the primary key as an integer.

Answer 3

No Terrorists No Racism No Recession No same sex marriages No Rape No Satan No natural disaters No Global Warming No World Hunger No Pro Sport Lockouts No Child Abuse, No Animal Abuse, No Woman Abuse More Cheaper Prices on Food, Gasoline, Clothing, Electronics & etc WWE and TNA rebranded as 'Total Nonstop World Wrestling' Lance Bass, Reichen Lehmkuhl, Spencer Duhm, Sam Champion and Matt Dallas living as women GSN divide into 2 channels(GSN Classic and GSN Modern) with the aide of Challenge TV, Fremantlemedia, Fremantlemedia Australia, Viacom, Seven Network, BBC, Channel 4(UK), CBS, Warner Bros, NBCUniversal and Sony Pictures Televison Comcast Digital, Charter Digital and AT&T U-verse united together as one big America's digital cable televison Most of the entire earth's landscape will be similar to the 1980s and 1990s

Answer 4

About the only way to do this is thru a multicolumn concatenated key or (as another suggests) using a trigger to stitch them together into a single column. However, why derive it this way? If you want to display it that way, then do it at execution time...no need to actually store the data that way.

Answer 5

You can't since you are using a string as a key.

Try to think again about the logic of storing the same character, in front of every field in a table. If you know it's gotta be there, why store it?? Just add it in your application if you reaaaally want it.