How to create Unique Fields in custom Tables

Pritam Gupta asked on July 17, 2017 06:58

Hello,

I want to create a unique field in custom table which doesn't accept duplicate value. Please guide me how to create a Unique Fields in custom Tables.

Thanks

Correct Answer

Pritam Gupta answered on July 18, 2017 06:21

Hello All,

I got a Solution for that, Now we have to do following Steps:-

1] Open the custom table and go to--> Fields

2] Select your Fields and Select Validation Rules--> General Conditions

3] After That add following code in the Code tab.

4] And Save it now you are done.

foreach (i IN GlobalObjects.CustomTables["customtable.TableName"].Items) {
 if (i.FieldName== FieldName.Value) {
  return false;
 }
}
return true;
------------------------------------------------------------

5] you can also do the same things for **Form** also

[Click Here](https://devnet.kentico.com/articles/tweaking-kentico-(2)-unique-fields)

1 votesVote for this answer Unmark Correct answer

Recent Answers

Trevor Fayas answered on July 17, 2017 19:38

Hi Pritam,

while Custom Module Classes have the ability to set "Unique", Custom tables by default do not allow that.

There are some options then that you can do:

Add the constraint manually via SQL

This option isn't a bad one, but it does need to be documented, and you cannot maintain it from within the CMS, and the error if someone tries to add something not unique may be ambiguous. Not recommended but quick and low skill needed.

Create a custom macro to check the value is unique using the form validation.

This will be the more 'Kentico' way of doing it. On fields you can Add Validation Rules to a field (Field must be required to run always), of type "General Condition"

Create a Custom macro, which will take the Table's GUID or ID, and that field's value you need to check uniqueness. Say the macro is called "bool CheckUniqueInMyTable(object ItemID, object Value)"

Call your custom macro for the field in the Validaiton Rules by entering CheckUniqueInMyTable(Fields["ItemID"], Fields["TheUniqueValueFieldName"])

Lastly, as you create your custom macro, you would do the following logic:

If the ItemIDis null (must be a new table), get any row that the value already exists. If you find a row, then it is not unique, return false. If you don't, return true it is unique.

If the ItemIDis NOT null (update), get any row that value already exists and the ItemIDdoes not equal the same one provided. If any found, not unique, return false. If none found, return true, it's unique.

More or less the equivelent of this SQL Statement

WHERE (IsNull(@ItemID) and MyUniqueColumn = @UniqueValue) OR (NOT IsNull(@TableID) and ItemID <> @TableID and MyUniqueColumn = @UniqueValue)

You can use the QueryInfoProvider.ExecuteQuery() function to do the above. Hope that helps!

2 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on July 17, 2017 21:55

SQL constraint is more natural and straight forward, to create constraint for table called customtable_SampleTable on field CustomID:

ALTER TABLE customtable_SampleTable
ADD CONSTRAINT UC_CustomID UNIQUE (CustomID);

You get the contstraint and nonclustered index!

If you are making a field unique i.e. most likely you going to query it - so the index will give you a performance benefit.

Just put in Field appearance/Explanation text something like 'UNIQUE' or 'HAS UNIQUE CONTRAINT', so it is always visible to an editor.

0 votesVote for this answer Mark as a Correct answer

Pritam Gupta answered on July 18, 2017 05:30

Hi, Peter M,

I tried your solution but now i got one issue that how can i display my custom error message while user enter duplicate value.Its display the default system error message "There was an error during save. (see more details)"

Please provide me the solution for that.

Thanks.

0 votesVote for this answer Mark as a Correct answer

Trevor Fayas answered on July 18, 2017 15:21

Pritam, i would think with your solution though, if you try to update an item (not save a new one) it would throw an error since it would find it's own row and return false.

You would need to adjust your macro to be:

if (i.FieldName== FieldName.Value && i.IdentityFieldName != IdentityFieldName.Value) {

0 votesVote for this answer Mark as a Correct answer