oracle primary key start with letter

I want my primary key of my table in oracle to start with a letter. The primary key is a nvharchar. A possible primary key might be: S1, S29191287开发者_开发知识库3123, S123123123. How can I do this? Trigger? Any example?

I agree with Justin Cave. There are also side-effects on the optimizer and index distribution. Those can be minimized by using a fixed length format with leading zeros (ie S0000913,S0000914,S0000915 rather than S913,S914,S915)

When an index block is 'full' and needs to be split to add a key, then if the block is on the right hand size of an index it gets 90/10 split. Otherwise it gets a 50-50 split. If you have a bunch of keys like S9, S90, S91, S92... S904..., then you get up to S6034 or something, you get a 50-50 split (because you've got all those S9 entries on the right hand side).

Similarly, when faced with a range scan 'S6034' fits between 'S6' and 'S7', though 6034 doesn't fit between 6 and 7.

Think twice about this. An alternative solution may be to have the 'S' component as a separate field that is either part of a two-column primary key or an attribute that isn't part of the key at all.

Do you always want to prepend 'S' to the generated primary key? Or is there some additional algorithm to figure out what letter to use? Or do you want the letter to be incremented over time?

If you are just prepending 'S', you could do something like

CREATE SEQUENCE your_pk_seq;

CREATE TRIGGER trg_populate_pk
  BEFORE INSERT ON your_table_name
  FOR EACH ROW
BEGIN
  SELECT 'S' || to_char( your_pk_seq.nextval )
    INTO :new.primary_key_column
    FROM dual;
END;

I would generally caution against doing this however. If you are generating a synthetic primary key, you shouldn't care about the format. It shouldn't matter to you functionally whether it is a number, a GUID, or something else. And since Oracle provides sequences in order to efficiently generate numeric synthetic keys, you really ought to let your synthetic primary keys be numeric. If the 'S' encodes some other bit of information, that really ought to be stored in a separate column (which you could always concatenate to the primary key for display purposes).

You can simply insert the value you wish to use as a PK.

INSERT INTO MYTABLE (PK) values ('S291912873123')