Prevent patching duplicates in SQL unique column

[Sienna]

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

[Sienna]

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]

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

[Sienna]

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]

@LanceDelano

[LanceDelano]

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]

@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]

@LanceDelano
Do you understand what I’m trying to say?

[LanceDelano]

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]

@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]

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.