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
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!