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.

asked 17 Feb '22, 08:17

Bud%20Durland%20MRP's gravatar image

Bud Durland MRP
330101224
accept rate: 25%


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'
permanent link

answered 17 Feb '22, 09:30

Christian%20Hamers's gravatar image

Christian Ha...
697131633
accept rate: 42%

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.

permanent link

answered 17 Feb '22, 09:39

Chris%20Keating's gravatar image

Chris Keating
7.8k49128
accept rate: 32%

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

(17 Feb '22, 10:05) Volker Barth

@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.

(17 Feb '22, 10:09) Bud Durland MRP

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.

permanent link

answered 17 Feb '22, 10:08

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 17 Feb '22, 11:14

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×24
×4

question asked: 17 Feb '22, 08:17

question was seen: 756 times

last updated: 17 Feb '22, 14:25