# How to verify no duplicate information is added to a SQL table **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [August 7, 2025, 2:30pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482 "2025-08-07T14:30:52Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Ros\_Alexander1](https://avatars.discourse-cdn.com/v4/letter/r/db5fbb/32.png) [@Ros\_Alexander1](https://forum.inductiveautomation.com/u/Ros_Alexander1)\ **Post date:** [August 7, 2025, 2:30pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/1 "2025-08-07T14:30:52Z")
I want the operator to input a job number into a text field. They will then hit a button that runs the named query for inserting the job number into a SQL database. I’m having issues with the operators inserting duplicate job numbers. Is this something I can catch before the query runs or do I need to script this within the named query? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/d/bda04966af3afeb0babe8a5e5af50ab49e80ab35.png) Named Query Script: ```sql INSERT INTO Arms_Internal_Schedule( JobNum) VALUES (jobNumInput) ``` ---
**Author:** ![Kevin\_Flener](https://avatars.discourse-cdn.com/v4/letter/k/74df32/32.png) [@Kevin\_Flener](https://forum.inductiveautomation.com/u/Kevin_Flener)\ **Post date:** [August 7, 2025, 2:41pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/2 "2025-08-07T14:41:52Z")
I do something like this: First, I have a named query: ```sql SELECT COUNT(*) AS IDCount FROM CalTrax_Assets WHERE AssetID = :IDPRM ``` Then, in my button’s script: ```python (params are defined up here)... id_check_result = system.db.runNamedQuery("CalTrax_ID_Check", {"IDPRM" : params["IDPRM"]}) id_exists = id_check_result.getValueAt(0, "IDCount") > 0 if not id_exists: (the rest of the script / run named query) ``` Edit: After looking at some of the responses below, I wouldn’t handle it this way going forward. I believe it' will still be safe for my application, since I’m not expecting multiple concurrent operators. I will probably change this in a future update though. ---
**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\ **Post date:** [August 7, 2025, 2:45pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/3 "2025-08-07T14:45:38Z")
I think it’s best to handle this with a stored procedure. Have your form call the stored procedure to do the insert. Have an if not exists in the stored procedure. This moves the business logic to your database which is a better architecture imo. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 7, 2025, 2:46pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/4 "2025-08-07T14:46:16Z")
Put a unique key constraint on the db table’s column in question. ---
**Author:** ![Ryan\_Deardorff](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan_deardorff/32/63815_2.png) [@Ryan\_Deardorff](https://forum.inductiveautomation.com/u/Ryan_Deardorff)\ **Post date:** [August 7, 2025, 2:48pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/5 "2025-08-07T14:48:14Z")
That depends. Do you want them to not be able to enter the duplicate ID altogether, or enter it with updated information? To update the existing info. you could use `REPLACE INTO` instead of `INSERT INTO`. Otherwise, I would run a `SELECT` query to check for the existence of the ID before doing the update, and if it exists, display an error message. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 7, 2025, 3:09pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/6 "2025-08-07T15:09:09Z")
> [@dkhayes117](#): > > unique key constraint Do this. Trap the exception if it is duplicate with `try: ... except Throwable, t:` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 7, 2025, 3:11pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/7 "2025-08-07T15:11:40Z")
> [@Steve\_Laubach](#): > > handle this with a stored procedure Yuck. I strongly disagree, for simple duplicate checking and handling. Singe SQL statements can do this. > [@Ryan\_Deardorff](#): > > I would run a `SELECT` query to check for the existence Definitely **not** this, as it is racy with multiple operators. ---
**Author:** ![Ryan\_Deardorff](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan_deardorff/32/63815_2.png) [@Ryan\_Deardorff](https://forum.inductiveautomation.com/u/Ryan_Deardorff)\ **Post date:** [August 7, 2025, 3:20pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/8 "2025-08-07T15:20:22Z")
I would do it as soon as the value in the text field changes, so the message can be displayed and the submit button disabled. But you’re right, you still need the unique key constraint as @dkhayes117 says, and proper error handling after the fact in case multiple operators submit the same ID at the same time. ---
**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\ **Post date:** [August 7, 2025, 4:08pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/9 "2025-08-07T16:08:07Z")
I looked into this deeper because I’ve generally done it with NOT EXISTS inside a sp in TSQL and I’ve never had a problem. I’m always down to learn new things and I know I can lean on your wealth of experience to teach me new things. I see that there can be race conditions in very large systems with many concurrent clients doing inserts. NOT EXISTS in TSQL is arguably faster than LEFT JOIN but I haven’t benchmarked it. I haven’t personally had a problem with this but I can see you could have a problem with this in an environment with a large number of concurrent operators pounding inserts. I assume that would be a bigger problem if you have really large tables too. I also saw that you can have a problem in some of the other DB vendors. I saw people saying MySQL particularly has a problem with this but I don’t have personal experience of this. We typically use MSSQL. The only real race condition and deadlocking issue I’ve seen with MSSQL is with SGAM contention. You don’t need a lot of concurrent users to encounter that one. ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [August 7, 2025, 4:08pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/10 "2025-08-07T16:08:55Z")
Doesn’t this also allow for a quicker/better feedback to the user than a stored procedure? If I remember correctly, calling an SP via a named query does not allow for a return value, but using the `try…except` would allow for custom feedback. ---
**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\ **Post date:** [August 7, 2025, 4:13pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/11 "2025-08-07T16:13:41Z")
It might vary by provider. You can return values from MSSQL sp’s in a few ways. Output parameters, return codes and you can even return multiple result sets but please don’t ever do that. I had a colleague for a short time that returned 7 resultsets from a sp once. It was not pretty. Edit: You can also use try/catch style logic in TSSQL. I generally like having the business logic in sp’s because it makes the front end simpler. If your db is a black box the front end is simple and easy to migrate to whatever you move to down the road. ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [August 7, 2025, 4:16pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/12 "2025-08-07T16:16:19Z")
LOL! I was thinking of the Named Query’s functionality. I remember wanting to return a `True/False` on a successful or failed `INSERT`, but the NQ does not allow feedback to the client/user. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 7, 2025, 5:07pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/13 "2025-08-07T17:07:00Z")
> [@Steve\_Laubach](#): > > I see that there can be race conditions in very large systems with many concurrent clients doing inserts. When PostgreSQL introduced their `ON CONFLICT` implementation, it was a big deal because they were the only brand that could do race-free single-statement conditional insert/update. All others, where syntax of some kind existed at all, had unhandled race conditions that could throw errors even when no conflict existed. (T-SQL MERGE was, maybe still is, especially bad.) Next best solution, across brands, is to unconditionally INSERT and let the DB’s UNIQUE index implementation enforce it for you. If you don’t have a unique constraint, and you do anything with multiple statements (including procedures) to test for existence, you can end up with duplicates. You can get close with strict transaction isolation, but you have to be ready to handle “unexplained” rollbacks. Don’t try to be smarter than the DB. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 7, 2025, 5:59pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/14 "2025-08-07T17:59:31Z")
MySQL/MariaDB has an on duplicate syntax ```sql INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) ON DUPLICATE KEY UPDATE column1 = new_value1, column2 = new_value2, ...; ``` ---
**Author:** ![Lynn\_Martineau](https://avatars.discourse-cdn.com/v4/letter/l/f1d935/32.png) [@Lynn\_Martineau](https://forum.inductiveautomation.com/u/Lynn_Martineau)\ **Post date:** [August 7, 2025, 7:32pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/15 "2025-08-07T19:32:40Z")
> [@pturmel](#): > > let the DB’s UNIQUE index implementation enforce it for you. If you don’t have a unique constraint, and you do anything with multiple statements (including procedures) to test for existence, you can end up with duplicates. You can get close with strict transaction isolation, but you have to be ready to handle “unexplained” rollbacks. > > Don’t try to be smarter than the DB. Let the database take responsibility for unique IDs instead of making the users responsible for that. Even the best operators will have problems if two are entering a new record at the same time. ---
**Author:** ![Ros\_Alexander1](https://avatars.discourse-cdn.com/v4/letter/r/db5fbb/32.png) [@Ros\_Alexander1](https://forum.inductiveautomation.com/u/Ros_Alexander1)\ **Post date:** [August 10, 2025, 1:26pm UTC](https://forum.inductiveautomation.com/t/how-to-verify-no-duplicate-information-is-added-to-a-sql-table/109482/16 "2025-08-10T13:26:32Z")
This worked like a charm!