Is it possible to create an identity column in Oracle without it being a primary key, and how does it relate to H2 Database?

user_6357780 0
Viewed 1562

I'm trying to transform a table to start using natural keys instead of surrogate keys, so before explaining what I'm trying to do, I'll explain how the database is currently set up.

-- FOO_BAR TABLE
id NUMBER(10) PRIMARY KEY, -- A sequence and a trigger is set up to this column.
uuid CHAR(36) UNIQUE

What I'm trying to do is:

  1. The id column should be deleted;
  2. The uuid column should be the primary key;
  3. A new column called creation_order should be created, and it should have the same values as id, but it'll not be the primary key.

So after the migration the table should look like this:

-- FOO_BAR TABLE
creation_order NUMBER(10) UNIQUE GENERATED AS IDENTITY,
uuid CHAR(36) PRIMARY KEY

The problem that made me create this question is that I'm using H2, and I should try to create the migration scripts the most pure SQL compliant as Oracle allows me, and since GENERATED AS IDENTITY is pure SQL and it's now supported by Oracle DB, I should try to stick with that.

So, my first question is:

In H2, I can't follow this approach, as GENERATED AS IDENTITY will always implicitly create a PRIMARY KEY constraint, as pointed in H2 Database Documentation:

Identity and auto-increment columns are columns with a sequence as the default. The column declared as the identity columns is implicitly the primary key column of this table (unlike auto-increment columns).

So for H2, I need to use AUTO_INCREMENT instead.

I looked for the documentation of Oracle DB and I didn't find any information about primary keys for the identity type, does it mean that Oracle's GENERATED AS IDENTITY work just as H2's AUTO_INCREMENT?

And if the answer is positive and GENERATED AS IDENTITY is different for each database, does anyone have any idea of how to use the same migration script for both databases, or is it impossible?

Thank you!

h2 oracle sql
1 Answers

Yes, it is possible (Oracle 12c):

CREATE TABLE tab (
  id INT PRIMARY KEY,
  some_identity  NUMBER GENERATED ALWAYS AS IDENTITY,  -- it is not PK
  descr VARCHAR2(30)
);

or with DEFAULT:

CREATE SEQUENCE seq;

CREATE TABLE tab(
    id INT PRIMARY KEY
   ,some_identity NUMBER DEFAULT seq.NEXTVAL
   ,descr VARCHAR2(30)
);
user_50708790
Related