# DB Tables and Unique or duplicate values

**URL:** <https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678>\
**Category:** Ignition\
**Created:** [October 14, 2020, 6:38pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678 "2020-10-14T18:38:56Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 14, 2020, 6:38pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/1 "2020-10-14T18:38:56Z")

</div>

Hi all,

New to Ignition and scripting / SQL. I am building a project to learn and I have run into a issue that I can solve with a query and an addition to a script, but I want to see if that is the best option.

To give you some background, here is my table structure…

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/3/b33861dd4eefc9278ea595ee8c93a0c9571b1a4b.png)

So I have a table component that sorts notes by…  
binder\_names \>\> topic\_names \>\> page\_names

To create a new page name, I take text and INSERT it into binder\_page table with a named query and a script on a button.

The binder\_page table allows duplicates because there might be multiple “Page 2” with different topics\_id. I did this so that as the binder\_notes table grows, the selection of the topic and page will not require querying the binder\_notes table. I am curious on thoughts about this choice.

Anyway, I now find that I can have duplicate page\_names with the same topic\_id. Now, I can add a SELECT query to the button script to check for duplicate names on that topic\_id just before I actually create the entry, but I am wondering if there is a different / better / less code way.

Any thoughts would be greatly appreciated. I can see that SQL and scripting will be a large part of any work done in Ignitions so I am doing all I can to learn all at once.

Thanks, Steven

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [October 14, 2020, 7:33pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/2 "2020-10-14T19:33:34Z")

</div>

My question would be, do you need to allow duplicates? For example your binder\_page, why do you need two entries with the same page and a topic\_id? I’m assuming your binder notes, the topic\_id there links to the one in in your binder page, if you just linked to your id in binder\_page then you can have less entries in your binder page and just join the two based on the id number from binder\_page being stored as topic\_id in your binder notes. This is the same with linking to other tables too. Your main table should store data that links to its related tables, but the related tables can have entries used more than once to limit the data you have to store.

Even as you use these in objects, your query that brings in the data to display can do the joins to display it the way you want then if you edit it, you can use what is displayed to look back and find out if you need to create a new entry or just link to an existing entry.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 14, 2020, 8:09pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/3 "2020-10-14T20:09:09Z")

</div>

@bpreston,

> [@bpreston](#):
>
> My question would be, do you need to allow duplicates? For example your binder\_page, why do you need two entries with the same page and a topic\_id?

I don't need a duplicate page with the same topic\_id, but I do need a duplicate page\_name with **different** topic\_id. So topic 1 and topic 2 may both have page 1.

In the same manner further up, binder 1 and binder 2 may both have topic 1.

> [@bpreston](#):
>
> I’m assuming your binder notes, the topic\_id there links to the one in in your binder page, if you just linked to your id in binder\_page then you can have less entries in your binder page and just join the two based on the id number from binder\_page being stored as topic\_id in your binder notes.

So the main table is binder.notes. Binder\_notes.binder\_id links to binder\_names.id.  
binder\_notes.topic\_id links to binder\_topics.id  
binder\_notes.page\_id links to binder\_page.id

In this manner I can query the binder\_names table to populate a dropdown.  
I can then use the a query to populate the topic\_name dropdown WHERE binder\_topics.binder\_id = binder\_names.id  
and finally I can populate the page-name dropdown WHERE binder\_page.topics\_id = binder\_topics.id

I hope this makes what I have more clear. Do I have too many tables? I know the binder.topics & binder.page tables will have some duplicate \_names, but no duplicate entries.

Thanks, Steven

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [October 14, 2020, 8:28pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/4 "2020-10-14T20:28:21Z")

</div>

I wouldn’t say you have to many tables. I just wanted to make sure your using them efficiently if that makes sense. Typically with relational tables, if items will be used multiple times then I would but them in their own table and link to them using their id(serial). If they are unique to the row on the main table, then I would keep them in the main table instead of linking to another table. That is where I was thinking you could be duplicating thing when linking from binder\_notes to binder\_page and binder topics since they but have an id which would be unique to each entry but then they have another id that I assume you create.

I was assuming that from your binder notes, you would do a join pulling binder\_notes.page\_id = binder\_page.id, this would allow you to return your value for the binder\_page.page\_name so I wasn’t sure where the binder\_page.topics\_id came into play. In the same way I had assumed that binder\_notes.topic\_id would be the same as binder\_topics.id allowing you to return binder\_topics.topic\_name. I hope that makes sense.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 14, 2020, 10:06pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/5 "2020-10-14T22:06:14Z")

</div>

I guess the question still remains, what is the best way to prevent creating duplicate binder.page entries?

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [October 15, 2020, 1:06pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/6 "2020-10-15T13:06:25Z")

</div>

That comes down to how you do the entry. Depending on how your doing it there are a few ways to do it.

If your doing the full entry at one time, when you do the entry you can have it look to see if the entry exists in your binder.page table and if it does, use the existing entry. If it doesn’t then create the new entry and log the id into your binder\_notes.page\_id.

If you want to allow a limited select to the users, you can have a table set up to view what exist. If you need to add a new one, you can have a button that creates a new blank entry, then you would have a script in your table onCellEdited that updates your database, when the entry is updated.

If I knew more about what you were picturing for a user interface, I could probably give a suggestion tailored to it.

---

<div class="post-metadata">

**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:** [October 15, 2020, 1:21pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/7 "2020-10-15T13:21:43Z")

</div>

This is what database Unique indices are for. That is the only reliable way. Your DB insert/update will fail if the unique index is violated.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 15, 2020, 1:40pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/8 "2020-10-15T13:40:08Z")

</div>

@pturmel

> [@pturmel](#):
>
> This is what database Unique indices are for. That is the only reliable way. Your DB insert/update will fail if the unique index is violated.

Here in lies my problem. My entry for the page\_names will actually be an id(serial/unique), a page\_name, and a topic\_id.

The page name cannot be unique because the same name could be used with many topic\_id.

The row will be unique but not the name or the topic\_id.

Am I looking at it wrong?

I can easily compare the new page name to to the table prior to writing it, but I was curious if my table structure could be improved.

Thanks for the help,

Steven

---

<div class="post-metadata">

**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:** [October 15, 2020, 2:05pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/9 "2020-10-15T14:05:32Z")

</div>

A Unique index in a DB can have multiple columns–the combination must be unique.

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [October 15, 2020, 2:06pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/10 "2020-10-15T14:06:14Z")

</div>

A unique index can be on a combination of columns – that won’t prevent duplicates in any single column, but it will prevent that combination from being duplicated.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 15, 2020, 2:18pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/11 "2020-10-15T14:18:33Z")

</div>

@pturmel & @KathyApplebaum,

> [@pturmel](#):
>
> A Unique index in a DB can have multiple columns–the combination must be unique.

> [@KathyApplebaum](#):
>
> A unique index can be on a combination of columns – that won’t prevent duplicates in any single column, but it will prevent that combination from being duplicated.

I was wondering that after I posted. SO, how do I do that? That would be a useful feature and perfect for this case.

Thanks, Steven

---

<div class="post-metadata">

**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:** [October 15, 2020, 2:23pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/12 "2020-10-15T14:23:14Z")

</div>

Look up your database’s CREATE INDEX statement. Syntax varies a bit from DB brand to brand.

Consider reading your DB’s online documentation cover-to-cover. Not for retention, but to give you an overview of what’s possible, so you can use appropriate search terms later. (:

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 15, 2020, 2:34pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/13 "2020-10-15T14:34:15Z")

</div>

Thanks @pturmel. I am spending a LOT of time going through manuals!! Between Ignition, PostgreSQL/SQL, and Python there is a never ending amount of knowledge.

I made it through the IU course in 10 days but still find myself going back through it looking for things I remember seeing but forgot how to do.

I am used to building and installing equipment, not programming it!! But times are a changing and I will as well.

Thanks for all the help and suggestions. I really appreciate it.

Thanks, Steven

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 17, 2020, 5:42pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/14 "2020-10-17T17:42:40Z")

</div>

> [@pturmel](#):
>
> A Unique index in a DB can have multiple columns–the combination must be unique.

> [@KathyApplebaum](#):
>
> A unique index can be on a combination of columns – that won’t prevent duplicates in any single column, but it will prevent that combination from being duplicated.

Ok, I have done some reading & testing and came up with this...

CREATE UNIQUE INDEX topic\_idx ON test\_index ((lower(topic\_name)), binder\_id)

This created an index of topic\_name and binder\_id. I included the lower() expression to help create actual unique topic names and not just versions of.

So the next question is looking ahead to the project, is there a way to capture the the "duplicate" error so that I can create a better error popup for the user? I do not see a specific exception to use in error handling.

This is the error I get...  
message tab...  
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "topic\_idx" Detail: Key (lower(topic\_name::text), binder\_id)=(topic 30, 31) already exists.

details tab...  
GatewayException: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "topic\_idx"  
Detail: Key (lower(topic\_name::text), binder\_id)=(topic 30, 31) already exists.  
caused by Exception: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "topic\_idx"  
Detail: Key (lower(topic\_name::text), binder\_id)=(topic 30, 31) already exists.

Ignition v8.0.16 (b2020082513)  
Java: Azul Systems, Inc. 11.0.7

I do not need the data from the error as my popup will be generic "you cannot have duplicate names".

Do I just assume that if I get an error it is a duplicate error?

Again, thanks for all the help!!

Steven

---

<div class="post-metadata">

**Author:** ![tyrel.parker](https://avatars.discourse-cdn.com/v4/letter/t/47e85d/32.png) [@tyrel.parker](https://forum.inductiveautomation.com/u/tyrel.parker)\
**Post date:** [October 20, 2020, 1:19pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/15 "2020-10-20T13:19:29Z")

</div>

I used this at one point to capture that error message for myself. You could modify this to output something to the user.

```auto
except Exception as e:
    msg = str(e.getCause())
    logger.info('except javalang: {}'.format( msg)
    if 'Cannot insert duplicate key' in msg:
        logger.info( 'duplicate')

```

---

<div class="post-metadata">

**Author:** ![ryanjmclaughlin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryanjmclaughlin/32/4318_2.png) [@ryanjmclaughlin](https://forum.inductiveautomation.com/u/ryanjmclaughlin)\
**Post date:** [October 20, 2020, 3:26pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/16 "2020-10-20T15:26:34Z")

</div>

This is where you get into more intricacies of application development. Generally in web development many follow a **MVC** (Model View Controller) architecture that separates out the logic of the View (GUI) from the Controller (logic programming) and the Model (objects/database). You could spend years reading different methods and philosophies on this.

I like to try to be proactive with my user interfaces to ensure that checks are run BEFORE the user submits to ensure that the data rules are not violated. Doing it this way you have a lot of control over the error. In your case as the user fills out the data, you might run a query in the DB to verify if the unique constraint is violated and prevent them from attempting the insert.

Another way would be to try to trap the specific error and reformatted it in a more friendly message to the user using try/except blocks like Tyrel pointed out.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 20, 2020, 3:39pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/17 "2020-10-20T15:39:05Z")

</div>

@ryanjmclaughlin,

> [@ryanjmclaughlin](#):
>
> I like to try to be proactive with my user interfaces to ensure that checks are run BEFORE the user submits to ensure that the data rules are not violated. Doing it this way you have a lot of control over the error. In your case as the user fills out the data, you might run a query in the DB to verify if the unique constraint is violated and prevent them from attempting the insert.

I thought about doing this. Would be easy enough but I thought I would ask the question in case there were easier or more "standard" way of doing it.

In this case, I like @tyrel.parker idea for two reasons.

1. I have multiple INSERT INTO to check. If I capture the error I can use the same code for each one.

2. I need to learn about Logger's and how to capture and repackage.

I am creating my first project just to learn so this is great stuff!!

Thanks for the help,

Steven

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 20, 2020, 4:02pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/18 "2020-10-20T16:02:40Z")

</div>

@tyrel.parker,

> [@tyrel.parker](#):
>
> ```auto
> except Exception as e:
> msg = str(e.getCause())
> logger.info('except javalang: {}'.format( msg)
> if 'Cannot insert duplicate key' in msg:
> logger.info( 'duplicate')
> 
> ```

I am trying to understand what is happening here. I read up on system.util.getLogger so it looks like you defined a Logger as "logger". I do not see the getClause() anywhere. Where can I find this? I assume it captures the text of the error message.

I also assume the logger.info is the actual message box.

My "plan" is to create the logger in the Window script so it is created when the window is open. Then capture it in the try...except each place I need it. Sound about right?

Thanks so much for the help,

Steven

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 20, 2020, 4:22pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/19 "2020-10-20T16:22:52Z")

</div>

`getCause()` is a method defined on [`java.lang.Throwable`](https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/lang/Throwable.html), the base class of all Java exceptions. Exceptions can be ‘chained’ via causes - that’s how you can get a rich stacktrace with many ‘Caused By’ lines.

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 20, 2020, 5:04pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/20 "2020-10-20T17:04:54Z")

</div>

@paul-griffith

> [@paul-griffith](#):
>
> `getCause()` is a method defined on [`java.lang.Throwable`](https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/lang/Throwable.html), the base class of all Java exceptions. Exceptions can be ‘chained’ via causes - that’s how you can get a rich stacktrace with many ‘Caused By’ lines.

Ok,...not sure what all that means but I am going to give it a test drive and see what happens. Looks like what @tyrel.parker laid out will work so I am going to try it.

Thanks!!

Steven

[Next page](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678.md?page=2)