cancel
Showing results for 
Search instead for 
Did you mean: 
0 Kudos

Prevent patching duplicates in SQL unique column

I have done some test in SQL table. There is no prevention against duplicate in unique SQL table columns. Validate function doesn't catch the error and Errors function returns blank. At the moment the app crash with errored patch code when duplicate is added into unique column in SQL

Status: Declined

Had some e-mail conversations with Sienna.  The way to deal with errors is with the  IsEmpty( Errors ( ... ) ). pattern.  This works.  The suggestion here was for a different way.  

Comments
Sienna
Memorable Member

I forgot to mentioned that mainly there is no prevention against duplicate when 2 users try to patch same data at the same time

LanceDelano
Power Apps

Are you using a primary key in your SQL table?  SQL Server should prevent duplicates if a PK is present.

Sienna
Memorable Member
Yes I have primary key in sql... please test it... It will crash your app when one of the column is set to unique. Please see this post as well when I have started to do some test with it

https://powerusers.microsoft.com/t5/General-Discussion/SQL-table-indexing-primary-date/m-p/95310
Sienna
Memorable Member
LanceDelano
Power Apps

SQL Server will not allow duplicates values in a PK column if properly set. A PK column always has a uniquess constraint set on it.  It will also not allow duplicate values in a column set with a uniqueness constraint.  

 

It appears that in your post you were playing with date/time as a PK key?  Is that still true?  

 

 

Sienna
Memorable Member
@LanceDelano
Yes PK pass... there is no problem with it.
And in my test app I have set name column as unique and when I tried to patch same name into the database it crashed the app. Please try it. Make a SQL table with one PK id and sencond column set to unique and try to patch same value into the second unique column. It will crash your app.. Validate don’t prevent it, and errors stays blank
Sienna
Memorable Member
@LanceDelano
Do you understand what I’m trying to say?
LanceDelano
Power Apps

Not fully.  My e-mail alias is lanced@microsoft.com.   Please send the exact schema and then the rows you are trying insert.   I see the schema so far as:

 

name     datatype   PK   Allow nulls

--------------------------------------------------

ID         ????          Yes       No

???      ????           No        No

 

 

And, then you're trying to insert some records.  Can you describe in detail?

 

You can e-mail directly. 

 

Thanks

Lance

Sienna
Memorable Member
@LanceDelano
Thanks for the email. I will do that and try to explain it as best as I can when I get home... It is really hard to do this things over the phone...
LanceDelano
Power Apps
Status changed to: Declined

Had some e-mail conversations with Sienna.  The way to deal with errors is with the  IsEmpty( Errors ( ... ) ). pattern.  This works.  The suggestion here was for a different way.