Q
Problem solve Get help with specific problems with your technologies, process and projects.

Distinguishing tables from one another

I've created automatically generated primary keys for my Oracle database using CREATE SEQUENCE. I was wondering whether it was possible to concatenate a character prefix to help distinguish individual tables from one another?

I've created automatically generated primary keys for my Oracle database using CREATE SEQUENCE. I was wondering whether it was possible to concatenate a character prefix to help distinguish individual tables from one another? (Such as B1001 for one table and C1001 for another.)
Unlike other database systems like SQL Server, Oracle does not automatically generate primary keys for you. You can create a sequence, and that sequence can be used to generate the next value in a primary key column, but this is not automatic; you have to create a trigger to store the sequence value in the table. You have complete control over this trigger's code. For instance, you an use code similar to the following to insert into the "B" table:
CREATE OR REPLACE TRIGGER b_pk_trig
  BEFORE INSERT ON b
  FOR EACH ROW
   pk_value VARCHAR2(20);
   seq_value NUMBER;
BEGIN
   -- Get the sequence next value
   SELECT my_sequence.NEXTVAL INTO seq_value FROM dual;
   -- Generate the primary key value
   pk_value := 'B' || seq_value;
   -- Modify the PK column to have this computed PK value.
   :new.pk_column := pk_value;
END;
/
You can code a similar trigger for your other tables as well.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close