CODEWITHSUNDEEP
sqloracle-database
Yihao Gao
Yihao Gao

Reputation: 553

Oracle SQL: "GENERATED ALWAYS" with a specified sequence

I have two tables that I would like to let them share the same sequence to populate the primary key ID column. However, I also don't want the user to specify or change the value for the ID column.

By using the code below, I can let two tables share the same sequence.

CREATE TABLE T1
(
    ID INTEGER DEFAULT SEQ_1.nextval NOT NULL
);

This code will use its own sequence and prevent users from changing or specifying with INSERT:

CREATE TABLE T1
(
    ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL
);

Is there a way that can both world? Something like this:

CREATE TABLE T1
(
    ID INTEGER GENERATED ALWAYS AS ( SEQ_1.nextval ) NOT NULL
);

Regarding the use case, as @Sujitmohanty30 asked, the reason that I raised this question:

I'm thinking to implement inheritance in the database, consider this UML diagram (I can't directly post images due to insufficient reputation, and sorry for being lack of imagination).

ANIMAL is abstract and all inheritance is mandatory. This means no instance of ANIMAL should be created. Furthermore, there is an one-to-many relationship between ANIMAL and ZOO_KEEPER.

Therefore, I came up with this idea:

CREATE SEQUENCE ANIMAL_ID_SEQ;

CREATE TABLE HORSE
(
    ID     INT DEFAULT ANIMAL_ID_SEQ.nextval NOT NULL PRIMARY KEY,
    HEIGHT DECIMAL(3, 2)                     NOT NULL
);

CREATE TABLE DOLPHIN
(
    ID     INT DEFAULT ANIMAL_ID_SEQ.nextval NOT NULL PRIMARY KEY,
    LENGTH DECIMAL(3, 2)                     NOT NULL
);

CREATE MATERIALIZED VIEW LOG ON HORSE WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON DOLPHIN WITH ROWID;

CREATE MATERIALIZED VIEW ANIMAL
    REFRESH FAST ON COMMIT
AS
SELECT 'horse' AS TYPE, ROWID AS RID, ID -- TYPE column is used as a UNION ALL marker
FROM HORSE
UNION ALL
SELECT 'dolphin' AS TYPE, ROWID AS RID, ID
FROM DOLPHIN;

ALTER TABLE ANIMAL
    ADD CONSTRAINT ANIMAL_PK PRIMARY KEY (ID);

CREATE TABLE ZOO_KEEPER
(
    NAME      VARCHAR(50) NOT NULL PRIMARY KEY,
    ANIMAL_ID INT         NOT NULL REFERENCES ANIMAL (ID)
);

In this case, the use of the shared sequence is to avoid collision in ANIMAL mview. It uses DEFAULT to get the next ID of the shared sequence. However, using DEFAULT doesn't prevent users from manually INSERTing the ID field or UPDATE the value of it.

Upvotes: 1

Views: 2043

Answers (3)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8665

You should normalize your data schema: add column animal_type into the table and create composite primary key on both columns

Upvotes: 0

Velocity
Velocity

Reputation: 475

You can create a master view/table and generate the sequence in it. Then copy it as column values into both tables while inserting.

Another option could be inserting into both tables at same time.Use SEQ.NEXTVAL to insert into first table to get a new ID, and then SEQ.CURRVAL to copy same id in the table.

Upvotes: 1

Atif
Atif

Reputation: 2210

No, you cant have anything like this because ID is independently generated for each of the tables and this can be done only using sequence when you are inserting the data in both the tables at the same time.

Upvotes: 0

Related Questions