cancel
Showing results for 
Search instead for 
Did you mean: 

Determine existence of Autoincrement

BudDurland
Participant
1,276

Is there a way for a function to determine if a given table is using an auto-increment field as it's primary key? I have a database that has some tables where the current Pkey is stored in a separate table, and other tables are using autoincrement. All PKeys are integer. I'm trying to make a single function that will return the next available PKey for a given table.

Accepted Solutions (1)

Accepted Solutions (1)

Chris26
Participant

You could check the system tables to see if a table has a column with an autoincrement default

select 1
  from sys.systable as t
       inner join sys.syscolumn as c
               on c.table_id = t.table_id
 where t.table_name = 'MyTableName'
   and c."default" = 'autoincrement'

Answers (2)

Answers (2)

VolkerBarth
Contributor

You can use get_identity() to get the next available autoincrement values in a safe manner. And you could also use it to check whether the table's PK has such a default, because it return NULL if it has not.

chris_keating
Product and Topic Expert
Product and Topic Expert

The SYSTABCOL "max_identity" column will provide you the max value of the autoincrement column.

This shows a list of the max identity for tables in the v17 demo database:

    select 
    su.user_name || '.' || st.table_name as TableName,
    stc.column_name as ColumnName,
    stc.max_identity as LastAutoincrement 
from 
    systabcol stc join systab st on stc.table_id = st.table_id join sysuser su on st.creator = su.user_id
where 
    su.user_name = 'GROUPO' and
    [default] in ( 'autoincrement', 'identity', 'global autoincrement' );

While it is possible to create a function to get the next primary key value, there will be challenges if done in a busy system. You should allow SQLA to handle the PK generation for tables with autoincrement defaults and only use a function to return a next PK for those tables not using autoincrement column types.

VolkerBarth
Contributor
0 Kudos

Is the max_identity value absolutely up-to-date? According to an older answer by Glenn himself, it's only updated on checkpoints...

BudDurland
Participant
0 Kudos

@Chris -- the challenge is that I did not design this database, though I have added tables to it. The ERP app that uses the DB expects to be able to get the key values for it's data from a 'key table'. The tables I've added all use autoincrement. The ERP does not use tables I've created, but some of my apps DO update the ERP tables.

My objective is to create a function that, when given a table will detect the presence of an autoincrement field and simply return the results of get_identity("TableName") if found. If not found, it will lookup & update the key value from the key table and return that.