Unique Constraints (on multiple columns)

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Unique Constraints (on multiple columns)

Post by jsetzer » 2018-07-25 13:25

Hi,

I'm wondering if there is a way to define multi-column unique constraints within appGini. From my experience this is a very common requirement for many master-detail scenarios and also for many n-m-relations.

it would be great if we could define those within appGini. After generation and publishing, the database should be migrated automatically or perhaps via "Utilities" / "Rebuild fields" in admin-area.

SQL:

Code: Select all

ALTER TABLE `TABLENAME`
ADD UNIQUE `IXNAME` (`COLUMN1`, `COLUMN2`);
...and...

Code: Select all

ALTER TABLE `TABLENAME`
DROP INDEX `IXNAME`;
How did others solve this?

Kind Regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Unique Constraints (on multiple columns)

Post by pbottcher » 2018-07-25 13:59

Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Unique Constraints (on multiple columns)

Post by jsetzer » 2018-07-25 14:46

Hi pböttcher,

thank you for your fast response and for directing me to the older discussion.

After all this conversation, did I get it right: ...

1. There is no built-in support for composite keys (unique constraints)

2. If we want them, we will have to create them on the database directly (using ALTER-statements as shown above) and we have to care for updates/migrations on ourselves.

3. As a workaround we can hook into beforeInsert / beforeUpdate functions to query the database for existance. If there is an identical record already, we can deny insert/update by returning FALSE.

Thinking about nr. 3 I'm wondering if the whole insert-procedure / update-procedure is encapsulated in a database transaction. In other words: are the beforeInsert and beforeUpdate hook function being executed within a transaction? If not, the 3rd opinion may become error-prone in multi-user environments during simultaneous updates.

Do you know if the hook functions are being executed within a transaction?

Kind Regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Unique Constraints (on multiple columns)

Post by pbottcher » 2018-07-25 16:27

Hi,

1, yes,
2, yes,
3, yes

And as far as I can see, hook functions are NOT being executed within a transaction.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Unique Constraints (on multiple columns)

Post by jsetzer » 2018-07-26 06:19

Thanks a lot!
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

sjohn
Veteran Member
Posts: 86
Joined: 2018-05-23 09:32

Re: Unique Constraints (on multiple columns)

Post by sjohn » 2018-07-26 09:32

Hello jsetzer, Ahmad, pböttcher ( and others )

If someone find a good solution for "simulating" combined key, as a way to check for "unique values" , I would very much like to see the solution ( the code ) for this. I have suggested a built-in feature in AppGini, but a good hook, could also do the job - as long as it can function in a multi-user environment.
I think it could be of common interest.
Kind regards
John

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Unique Constraints (on multiple columns)

Post by jsetzer » 2018-07-28 17:22

I've tried a couple of variations but none of them seems 100% bullet-proof. Here are my current intermediate results:

a) UNIQUE CONSTRAINT on database
I have created a UNIQUE CONSTRAINT on multiple columns in the database. Inserts and updates are beeing denied by the database now. This works of course and from my point of view this should be definately be done in any case just to keep data integrity.

Create a new unique constraint:

Code: Select all

ALTER TABLE `recordpersons`
ADD UNIQUE `record_id_person_id_role_id` (`record_id`, `person_id`, `role_id`);
Drop an existing unique constraint:

Code: Select all

ALTER TABLE `recordpersons`
DROP INDEX `record_id_person_id_role_id`;
But there is a problem here: On Insert/Update in AppGini the users leave the details-view and get redirected to the table-view with an alert-danger (notification) telling that the record could not be saved. All the input is gone then. :?


b) BeforeInsert/BeforeUpdate-hook
I can validate in PHP and return FALSE.
Problem here: the same as above.


c) Replace the onclick event handler of the submit button(s)
I can do client side validation and perhaps cancel submitting the data. Problem here: I cannot do server side validation using AJAX because of the "A" in "AJAX". The form will be submitted and does not wait for the AJAX request to be completed. (Using the deprecated "async: false" is not an option for me due to various side effects)


d) AJAX call with server-side validation as a "pre-check"
Im my sample case I have three Lookups (dropdowns). My composite key contains those three fields. On change of any of the dropdowns I POST the selected values via AJAX, perform a database query as server-side check, return the query result as JSON to the client and - depending on the JSON-result - mark the three fields as "warning" or "success".

One thing is much better now: Users get informed about duplicate keys BEFORE they click save-button.

Here is what (d) looks like:
2018-07-28_18-40-52.gif
2018-07-28_18-40-52.gif (67.47 KiB) Viewed 17977 times

If the user tries to save the record anyway, the record will not be stored due to the UNIQUE CONSTRAINT and he will be redirected to table view by appGini.

Of course there is the old and well known problem of concurrent data access in a multiuser environment. The result of the pre-check is correct at the time of the pre-check but perhaps not a second later at the time of the insert-/update-trial. So if user B inserts/updates a record with identical keys after the pre-check but before the insert/update, the insert/update will fail.

Summary

1. From my point of view UNIQUE CONSTRAINTS should be created on the database in any case to keep integrity.

2. The pre-check functionality is not 100% perfect but may help users in most of the cases.


I wish users could be redirected to details-view after an insert/update failed and see their entered values there. Please feel free to give me some hints if there is a way without touching the overwritable files.

Hope this helps someone!

Kind Regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

khouse
Posts: 11
Joined: 2019-05-12 14:34

Re: Unique Constraints (on multiple columns)

Post by khouse » 2020-02-07 23:31

Reviving this thread with a question. I basically want to prevent a user from selecting the same value twice on a simple table with a lookup being the only selectable value, the logged in user can only see their own data. It looks to me like I need to create a Unique Constraint directly on the database but I have a problem... Selecting the same item twice spreads the data across two tables. In my case the players table and membership_userrecords. The third table contains the looked up data.

So a simple constraint will not suffice as there isn't a single table containing duplicate data. A join exposes the duplicate data but I'm completely stuck here so any help would be greatly appreciated! The customer is unsure why preventing duplicates is such a problem and I'm inclined to agree.

tminh
Posts: 28
Joined: 2019-05-23 02:26

Re: Unique Constraints (on multiple columns)

Post by tminh » 2020-09-27 02:21

jsetzer wrote:
2018-07-28 17:22
d) AJAX call with server-side validation as a "pre-check"
Im my sample case I have three Lookups (dropdowns). My composite key contains those three fields. On change of any of the dropdowns I POST the selected values via AJAX, perform a database query as server-side check, return the query result as JSON to the client and - depending on the JSON-result - mark the three fields as "warning" or "success".

One thing is much better now: Users get informed about duplicate keys BEFORE they click save-button.

Here is what (d) looks like:

2018-07-28_18-40-52.gif
Hi, can you share me the code, how to do this ??

thanks

Post Reply