Auto-increment primary keys in SQL

Question

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.

The typical way you use an auto increment primary key column is to just omit the column name from the insert statement. Then, the database will automatically populate it with the next value in the sequence. What you are trying to do would only be an issue if you needed to use that primary key value to e.g. insert into a second table. — Tim Biegeleisen, Aug 02 '19 at 01:52
"*Multiple tables use the same sequence of auto-generated primary keys when user creates a static data record from the GUI*". Multiple tables are sharing the same sequence? Are you saying if I insert into tableA I'll get N and if I insert into tableB I'll get N+1? If so, why? — Schwern, Aug 02 '19 at 01:52
@Schwern I have no idea why that is the case. Our product is developed by a third party vendor and that is how they've designed it. I'm a product BA filling in for our Tech BA who left the team. Hence the struggle :( — phoenix, Aug 02 '19 at 02:02
@TimBiegeleisen That'd be awesome if it works. I'll try it in the DEV env. — phoenix, Aug 02 '19 at 02:03
Related: [How to create id with AUTO_INCREMENT on Oracle?](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) — , Aug 02 '19 at 02:07

Bob Jarvis - Слава Україні · Answer 1 · 2019-08-02T03:20:02.950

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

Schwern · Accepted Answer · 2019-08-02T03:30:41.543

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.

Thank you @Schwern. I found out that the parent table was using a trigger to create sequential PKs for a dffierent table. I asked the DBA to modify the trigger code to include my table as well. No need to insert manually now. — phoenix, Aug 02 '19 at 05:56
@phoenix Glad you got it working. Trigger based primary keys are old school, slow and complex. — Schwern, Aug 02 '19 at 18:49

Auto-increment primary keys in SQL

2 Answers2