CODEWITHSUNDEEP
sqloracle-databaseprimary-keyoracle12cauto-increment
phoenix
phoenix

Reputation: 36

Auto-increment primary keys in SQL

I need help with the insert statements for a plethora of tables in our DB. New to SQL - just basic understanding

Summary:

Table1

Col1 Col2   Col3
1    value1 value1 
2    value2 value2
3    value3 value3

Table2

Col1 Col2   Col3
4    value1 value1 
5    value2 value2
6    value3 value3

Multiple tables use the same sequence of auto-generated primary keys when user creates a static data record from the GUI.

However, creating a script to upload static data from one environment to the other is something I'm looking for.

Example from one of the tables:

Insert into RULE (PK_RULE,NAME,RULEID,DESCRIPTION) 
values 
(4484319,'TESTRULE',14,'TEST RULE DESCRIPTION')

How do I design my insert statement so that it reads the last value from the PK column (4484319 here) and auto inserts 4484320 without explicitly mentioning the same?

Note: Our DB has hundreds and thousands of records.

I think there's something similar to (SELECT MAX(ID) + 1 FROM MyTable) which could potentially solve my problem but I don't know how to use it.

Upvotes: 0

Views: 3904

Answers (2)

Schwern
Schwern

Reputation: 165416

Multiple tables use the same sequence of auto-generated primary keys when user creates a static data record from the GUI.

Generally, multiple tables sharing a single sequence of primary keys is a poor design choice. Primary keys only need to be unique per table. If they need to be unique globally there are better options such as UUID primary keys.

Instead, one gives each table their own independent sequence of primary keys. In MySQL it's id bigint auto_increment primary key. In Postgres you'd use bigserial. In Oracle 12c it's number generated as identity.

create table users (
    id number generated as identity,
    name text not null
);

create table things (
    id number generated as identity,
    description text not null
);

Then you insert into each, leaving off the id, or setting it null. The database will fill it in from each sequence.

insert into users (name) values ('Yarrow Hock');              -- id 1
insert into users (id, name) values (null, 'Reaneu Keeves');  -- id 2

insert into things (description) values ('Some thing');            -- id 1
insert into things (id, description) values (null, 'Shiny stuff'); -- id 2

If your schema is not set up with auto incrementing, sequenced primary keys, you can alter the schema to use them. Just be sure to set each sequence to the maximum ID + 1. This is by far the most sane option in the long run.

If you really must draw from a single source for all primary keys, create a sequence and use that.

create sequence master_seq
  start with ...

Then get the next key with nextval.

insert into rule (pk_rule, name, ruleid, description) 
values (master_seq.nextval, 'TESTRULE', 14, 'TEST RULE DESCRIPTION')

Such a sequence goes up to 1,000,000,000,000,000,000,000,000,000 which should be plenty.

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50057

The INSERT and UPDATE statements in Oracle have a ...RETURNING...INTO... clause on them which can be used to return just-inserted values. When combined with a trigger-and-sequence generated primary key (Oracle 11 and earlier) or an identity column (Oracle 12 and up) this lets you get back the most-recently-inserted/updated value.

For example, let's say that you have a table TABLE1 defined as

CREATE TABLE TABLE1 (ID1   NUMBER
                       GENERATED ALWAYS AS IDENTITY
                       PRIMARY KEY,
                     COL2  NUMBER,
                     COL3  VARCHAR2(20));

You then define a function which inserts data into TABLE1 and returns the new ID value:

CREATE OR REPLACE FUNCTION INSERT_TABLE1(pCOL2  NUMBER, vCOL3 VARCHAR2)
  RETURNS NUMBER
AS
  nID  NUMBER;
BEGIN
  INSERT INTO TABLE1(COL2, COL3) VALUES (pCOL2, vCOL3)
    RETURNING ID1 INTO nID;

  RETURN nID;
END INSERT_TABLE1;

which gives you an easy way to insert data into TABLE1 and get the new ID value back.

dbfiddle here

Upvotes: 2

Related Questions