SQL: Oracle 19c unique ID-CodePudding

I'm currently trying to understand how to generate Unique ID in SQL like HID0014. I can't seem to find any resources or documentation to help with this problem except answer in:

How to automatically generate unique id in SQL like UID12345678?

CREATE TABLE dbo.tblUsers
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    UserID AS 'UID'   RIGHT('00000000'   CAST(ID AS VARCHAR(8)), 8) PERSISTED, 
    [Name] VARCHAR(50) NOT NULL,
)

I have tried using it in my Oracle 19c SQL plus to generate a table but I am getting errors. Can someone explain if I am using the wrong syntax? Or the code provided above is invalid for Oracle 19c.

CodePudding user response：

Use an identity column and a virtual column:

CREATE TABLE table_name
(
  id     INT GENERATED ALWAYS AS IDENTITY
         NOT NULL
         CONSTRAINT table_name__id__pk PRIMARY KEY,
  userid VARCHAR2(11)
         GENERATED ALWAYS AS (
           CAST('UID' || TO_CHAR(id, 'FM00000000') AS VARCHAR2(11))
         ),
  name   VARCHAR2(50)
);

Then:

INSERT INTO table_name (name)
SELECT 'Alice' FROM DUAL UNION ALL
SELECT 'Beryl' FROM DUAL UNION ALL
SELECT 'Carol' FROM DUAL;

SELECT * FROM table_name;

ID USERID NAME

1 UID00000001 Alice

2 UID00000002 Beryl

3 UID00000003 Carol

ID	USERID	NAME
1	UID00000001	Alice
2	UID00000002	Beryl
3	UID00000003	Carol

db<>fiddle here