Comment créer un identifiant avec AUTO_INCREMENT sur Oracle ?

Question

Il semble qu'il n'existe pas de concept d'AUTO_INCREMENT dans Oracle, jusqu'à la version 11g incluse.

Comment puis-je créer une colonne qui se comporte comme un auto-incrément dans Oracle 11g ?

Answer 1

Les colonnes "auto_incrément" ou "identité" n'existent pas dans Oracle. Cependant, vous pouvez le modéliser facilement avec une séquence et un déclencheur :

Définition du tableau :

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

Définition du déclencheur :

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

UPDATE : La colonne IDENTITY est maintenant disponible sur la version Oracle 12c, voir ceci :

CREATE TABLE t1 (c1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
                   c2 VARCHAR2(10));

Answer 2

SYS_GUID renvoie un GUID - un identifiant unique au monde. A SYS_GUID est un RAW(16) . Il ne génère pas de valeur numérique incrémentale.

Si vous voulez créer une touche numérique incrémentale, vous devez créer une séquence.

CREATE SEQUENCE name_of_sequence
  START WITH 1
  INCREMENT BY 1
  CACHE 100;

Vous pouvez ensuite utiliser cette séquence dans votre INSERT déclaration

INSERT INTO name_of_table( primary_key_column, <<other columns>> )
  VALUES( name_of_sequence.nextval, <<other values>> );

Ou vous pouvez définir un déclencheur qui remplit automatiquement la valeur de la clé primaire en utilisant la séquence

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT name_of_sequence.nextval
    INTO :new.primary_key_column
    FROM dual;
END;

Si vous utilisez Oracle 11.1 ou une version ultérieure, vous pouvez simplifier un peu le déclencheur

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.primary_key_column := name_of_sequence.nextval;
END;

Si vous voulez vraiment utiliser SYS_GUID

CREATE TABLE table_name (
  primary_key_column raw(16) default sys_guid() primary key,
  <<other columns>>
)

Answer 3

Dans Oracle 12c et plus, vous pourriez faire quelque chose comme,

CREATE TABLE MAPS
(
 MAP_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
 MAP_NAME VARCHAR(24) NOT NULL,
 UNIQUE (MAP_ID, MAP_NAME)
)

---

Et dans Oracle (Pre 12c).

-- create table
CREATE TABLE MAPS
(
 MAP_ID INTEGER NOT NULL ,
 MAP_NAME VARCHAR(24) NOT NULL,
 UNIQUE (MAP_ID, MAP_NAME)
)
--create sequence
CREATE SEQUENCE MAPS_SEQ;
-- create tigger using the sequence
CREATE OR REPLACE TRIGGER   MAPS_TRG 
BEFORE INSERT ON MAPS 
FOR EACH ROW
WHEN (new.MAP_ID IS NULL)
BEGIN
  SELECT MAP_ID_SEQ.NEXTVAL
  INTO   :new.MAP_ID
  FROM   dual;
END;
/
--enable the trigger
ALTER TRIGGER MAPS_TRG ENABLE ;

Answer 4

Trigger et Sequence peut être utilisé lorsque vous voulez un numéro sérialisé que tout le monde peut facilement lire/se souvenir/comprendre. Mais si vous ne voulez pas gérer la colonne ID (comme emp_id) de cette façon, et que la valeur de cette colonne n'est pas très importante, vous pouvez utiliser la méthode suivante SYS_GUID() à la création de la table pour obtenir l'incrément automatique comme ceci.

CREATE TABLE <table_name> 
(emp_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
name VARCHAR2(30));

Maintenant, votre emp_id acceptera la "valeur de l'identifiant unique mondial". vous pouvez insérer une valeur dans la table en ignorant la colonne emp_id comme ceci.

INSERT INTO <table_name> (name) VALUES ('name value');

Ainsi, il insérera une valeur unique à votre emp_id Colonne.

Answer 5

À partir d'Oracle 12c, les colonnes d'identité sont prises en charge de deux manières :

1. Séquence + Tableau - Dans cette solution, vous créez toujours une séquence comme vous le feriez normalement, puis vous utilisez la DDL suivante :

   CREATE TABLE MyTable (ID NUMÉRO DEFAULT MonTable_Seq.NEXTVAL , ...)

2. Table seulement - Dans cette solution, aucune séquence n'est explicitement spécifiée. Vous utiliseriez la DDL suivante :

   CREATE TABLE MyTable (ID NUMBER GÉNÉRÉ EN TANT QU'IDENTITÉ , ...)

Si vous utilisez la première méthode, elle est rétrocompatible avec la façon existante de faire les choses. La seconde est un peu plus directe et plus conforme au reste des systèmes de SGBDR existants.