Autoincrement Primary Key for Oracle

A lot of databases has a column autoincrement attribute or an autoincrement data type. They are used to create unique identifiers for a column. In Oracle, you must use a Sequence in combination with a Trigger to get an autoincrement column. DeZign for Databases will automate the creation of an autoincrement column for you.

To make an Oracle autoincrement column in DeZign for Databases:

1. Select Dictionary | Sequences from the application menu.
2. In the Sequences dialog, add a new sequence (let's say Sequence_1). Press OK to save your changes.
3. Go to the Entity dialog of the entity which owns the Attribute(column) you want to make autoincrement.
4. Select the Attribute in the list of attributes.
5. Select a numeric data type for the Attribute (NUMBER, INTEGER).
6. Go to tab "Advanced" in the property editor at the bottom of the list of attributes.
7. Select "Sequence_1" in the Apply sequence on attribute field.
8. A "autoincrement" trigger will be created now automatically. This trigger will use the selected Sequence. Go to tab Triggers to see the generated trigger.

The generated trigger code:

CREATE OR REPLACE TRIGGER %triggername%
BEFORE INSERT ON %tablename% FOR EACH ROW
BEGIN
  SELECT %seqname%.NEXTVAL
  INTO :NEW.%columnname%
  FROM DUAL;
END;

%seqname% will be replaced with the name of the sequence when you generate the database.

%triggername% will be replaced with the name of the trigger when you generate the database.

%columnname% will be replaced with the name of the associated column when you generate the database.

Note that the sequence can be reused. You can use the same sequence on multiple columns if you want.

The generated trigger code is generated based on the template settings in the oracle definition file (oracle7.def, oracle8.def, oracle9.def, oracle10.def). The definition files are located in the Definitions subdirectory of DeZign for Databases' installation directory.


See also: About Primary Keys and auto increment columns