I have a user interface where an employee is adding a record for work request. Once submitted it goes into a SQL table for a supervisor to poll later. I am trying to add a unique ID per record. Does anyone have any suggestions?
Set the ID field to be auto incrementing in the SQL definition
2 Likes
So I created a column called WorkRequestID and you are saying set it to auto increment?
A couple of other options…
from java.util import UUID
newID = UUID.randomUUID().getMostSignificantBits()
or for sequential IDs SQL auto increment is a really good option, but you can also try something like this
"""An atomic, thread-safe incrementing counter."""
import threading
class AtomicCounter:
"""
>>> counter = AtomicCounter()
>>> counter.increment()
1
>>> counter = AtomicCounter()
>>> counter.value
42
>>> counter.increment()
43
>>> counter = AtomicCounter()
>>> def incrementor():
... for i in range(100000):
... counter.increment()
>>> threads = []
>>> for i in range(4):
... thread = threading.Thread(target=incrementor)
... thread.start()
... threads.append(thread)
>>> for thread in threads:
... thread.join()
>>> counter.value
400000
"""
def __init__(self, initial=0):
"""Initialize a new atomic counter to given initial value (default 0)."""
self.value = initial
self._lock = threading.Lock()
def increment(self):
"""Atomically increment the counter by 1 and return the
new value.
"""
self._lock
self.value += 1
return self.value
Yep, that is correct. It is also good to make that column an index too. It doesn't have to be the primary key, but it can be.
1 Like
As a general rule I follow, even if you don’t think the table needs or requires an auto incrementing primary key - make it anyways. I recently ran into a situation where I had to add an auto incrementing primary key in SQL Server and that requires a whole table rebuild, which is a bit annoying. It costs you nothing to have the column and will prevent a future headache if/when it does become something you need.
3 Likes
I do not create auto-incrementing columns if the data naturally has a column or combination of columns that uniquely identify each row. I make those the primary key.
Yep, the Ignition SQL query browser in Designer won’t let you edit data without a PK, another good reason to use one
Fair enough but I think this is one of those things where you need to fully be aware of all the implications and comfortable with the rule to know when it’s an appropriate time to not use it. But if a developer isn’t sure or is newer to sql, I would very much like them to use an identity column to be on the safe side (if I am working with them).
I feel strongly about this as I just dealt with a table that had a column called “ID” that was the primary key, but not auto incrementing, and actually had a semantic meaning (the value is used to denote a testing grade), so when I was asked to make the table INSERT ONLY and keep track of historical changes, I realized the problem I had. Took like 80-ish lines of SQL Server to rebuild a new table and insert into the existing information. I have to do this for another 100 or so tables. So in this moment I have some very strong opinions about it and whomever designed the table like this in the first place.
1 Like
I can understand that, but your case seems pretty extreme. Where I encounter it most is in time-series tables. Those should have a PK that is t_stamp possibly with a group id (or value id for tall tables). Time series tables should not have an auto-increment ID. Yeah, old MySQL couldn’t do a multi-column PK. That by itself is cause to never use it.