Prevent Duplicate Values on Combination of Two Fields

[LornaM]

I have a table called Households which has an autonumber field called lngHouseholdID. Normally I would set this as the key field. However, I need to make sure that as records are being entered the combination of the strHouseholdAddress field and strHouseholdZip fields are never duplicated. I have done this by setting these two fields together as the key field. Is this the best way to do what I want to do? Just need some validation that I'm on the right track.

Thanks

[CraigDolphin]

That is certainly a valid way of doing it IMO

An alternative is to keep the auto as your pk, and simply create a two-field index and not allow duplicates. (to do this you would go to the table in design view, find the icon that looks like lines of writing to the left of a lightning bolt, and click it to open the indexes dialog box for that table.

Then add a new index name below the last index that's in the table, and then add two fields in the field name column (you only need to put the index name once). Then make sure that the index has a setting of Yes for unique. It does not have to be your primary index.

I only mention this to help you know the options.

[LornaM]

Now I feel really dumb! I knew that, but just forgot. Guess I've been out of the loop for too long! My sons have resurrected me from retirement to do this project for them. I haven't done much DB development for about five years, so I'll have to admit that I'm a little rusty. Thanks for helping to dust the cobwebs off my brain!

[LornaM]

So, I have another question. As soon as I'm done developing the database, it will be converted to MySQL (of which I have no experience). Do you know if the multiple indexes will convert over okay?

[CraigDolphin]

Sorry, I have never used MySQL. I think (can't swear but vaguely recall...) that the Access - SQL Server wizard does convert the indices over but you'd have to check to be sure. So, I imagine if there's a converter for MySQL they'd cover that too.

[Matrix]

Multiple indexes are supported in MySQL. But I would do it in a different way - check the data in your code other than rely on database. After the user input the data, check if it already exists in the table, if it does, display an error message. It's very easy to do, just a simple 'select' query. It's also very flexible, just update the code if you need any changes in the future.

[CraigDolphin]

You can do as Matrix suggests but be damned sure to prevent anyone being able to edit data directly in your tables if you go this route because otherwise you could end up with unexpected results caused by not enforcing the unique combination of fields at the table level.

Of course, this is normal practice anyway. But it is doubly important if you are relying only on front-end code as your only guarantee that your data assumptions are valid.

[Matrix]

CraigDolphin: Thanks for pointing out the flaw, it is important. Usually I don't worry about someone will modify the database directly, but other programmers should be well informed.

On the other hand, if we only rely on database - just try to insert the data and see if it succeeds, the return code may not tell the exact reason why it fails (or too database dependent). Also, I always have the feeling that failed database query is bad in terms of performance, since code execution time can be ignored compared to Database query, I do all possible data check before I send out the DB query .

[CraigDolphin]

A good point too I'm less experienced with web-based applications but you're undoubtedly correct.