Oracle and auto_increment / identity

Question

In modern versions of Oracle, there is some “standard” (stored procedure, optional CREATE syntax, etc.) way to set up a table using the auto_increment / identity style column, or we are still stuck manually by creating a table, creating sequences, and creating a trigger.

Update . I understand that Oracle has no concept of auto_increment. I am interested in the fact that if any of the standard Oracle tools have automated the creation of a sequence and trigger, or if the database administrator is left to create the necessary queries / commands to create a sequence and initiate themselves.

Answer 1

If you want to sequentially increase the ordered values, then no, the SEQUENCEonly choice.

If you only need a personality, use SYS_GUID()

Answer 2

Oracle SQL Developer gives you the opportunity to automatically create a code to create or replace a trigger that fills the primary key of a table from a sequence. To do this, right-click the table name> Trigger> Create (PK from sequence) from the navigator tree. However, it does not create consistency for you.

Answer 3

, . , .

Answer 4

Oracle 12c onward -

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)
)

---

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 5

? , . (, , , ).

GUID. Oracle SYS_GUID(), .