on 2022 Feb 17 8:17 AM
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.
Request clarification before answering.
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Is the max_identity value absolutely up-to-date? According to an older answer by Glenn himself, it's only updated on checkpoints...
@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.
User | Count |
---|---|
50 | |
10 | |
8 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.