For multiple fields that together must be unique but individually can have the same values across different records, I'm wondering if there is an alternative to multi-field indexing and setting it to unique. I know that indexing increases the size of the table and adds overhead as well. However, it's the only way I know of to keep single and multiple fields unique.
For one or two fields, I'm sure there's no problem with indexing them. But sometimes the case arises where I need to make 3 or 4 fields together as unique.
This is a common requirement, and this is accomplished by selecting each of the fields to be included, by holding down the Ctrl key when making the selection. Then, click on the Key icon and this will define a compound index where a combination of the fields will be unique. In addition, you can define each of the selected field indexes in their properties, whether they can contain a Null value or must not be empty.
I know HOW to make a multi-field index, even without making it the primary key. What I'm interested in is the overhead and other cons to doing this. Will its usefulness be outweighed by its problems? Also, if this is the only method, then I'll have to use it. But if there's a way to do it programmatically that wouldn't require the same overhead as multi-field indexing, I'd much rather do that.