Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    156

    Unanswered: Index uniqueness: Alternatives?

    Hello all:

    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.

    Any suggestions would be greatly appreciated!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    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.

  3. #3
    Join Date
    Jul 2004
    Posts
    156
    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.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  4. #4
    Join Date
    Jul 2004
    Posts
    125
    Compund indexing is the easiest and the most efficient way and the least overhead - by using the built in features of the Jet engine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •