Please help me figure out how to configure the Oracle ID field with PK and automatic number generation when creating a table in Oracle Database. Thank.

  • In Oracle, this requires a trigger + sequence. Create a table when you connect through what? in OracleSqlDeveloper in the Advanced mode of creating the table, it is enough to select the field for the PC and tick the trigger and the sequence will be created automatically. - 4per
  • Show how you create a table or attach a creation script - Viktorov
  • @ 4per I create a table in TOAD. - Ethernets
  • @lDrakonl CREATE TABLE GRAPH ( UKEY INTEGER, U_RUL INTEGER, K INTEGER, T DATE, H4 NUMBER, H1 NUMBER, V1 NUMBER, V2 NUMBER, V3 NUMBER, V4 NUMBER, VM NUMBER, T1 NUMBER, T2 NUMBER, T3 NUMBER, T4 NUMBER, DL INTEGER, IREZ INTEGER, FREZ NUMBER, LOGBITS INTEGER ) - Ethernets
  • @lDrakonl but I also tried to create in TOAD ... I tick off it with another PK - Ethernets

1 answer 1

In the version before the 12th oracle you will have to additionally create a sequence and a trigger. Together, it looks like this: Creating a table:

 CREATE TABLE GRAPH ( UKEY INTEGER, U_RUL INTEGER, .... LOGBITS INTEGER )

Adding an index:

 ALTER TABLE GRAPH ADD (CONSTRAINT GRAPH_PK PRIMARY KEY (UKEY));

Creating a sequence:

 CREATE SEQUENCE graph_seq START WITH 1;

Create trigger:

 CREATE OR REPLACE TRIGGER graph_id BEFORE INSERT ON GRAPH FOR EACH ROW BEGIN SELECT graph_seq.NEXTVAL INTO :new.UKEY FROM dual; END;

In version 12 of Orakla, when creating a table, you can immediately write this:

 CREATE TABLE GRAPH (UKEY INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY, ... );
  • Thanks, it works, but I don’t understand, if I delete the fields from the table, the key continues from the last one, which was well, for example, there were 8 entries, I deleted them all, add a new one and get the key number 9 on a non-1 why? - Ethernets
  • one
    There is a sequence inside that gives numbers in order. And if she gave number 8, then the next one will be 9. It does not depend on the table in which the rows are deleted. Typically, for a field with an ID, uniqueness is important, and the order of numbers is not important. If my explanation is not clear, it is better to read about the sequence - Viktorov