How to stop duplicate records in a database

Duplicates are stopped by making the database refuse them. In Base by Zaweba you tick unique on a field in the table designer when a single value must never repeat, such as an email address or a membership number. When the rule involves more than one field, open Rules for the table and add a unique index over several fields, for example one order number per customer.

Either way, the check lives in the database itself, so a second copy can't sneak in through a form, the datasheet, an import or a script.

Unique on a single field

Some values identify a record on their own: a membership number, a product code, a vehicle registration, an email address used as a login. Open the table in the designer, choose the field and tick unique. From then on, typing a value that already exists is refused.

There is also an indexed option without unique. That doesn't stop repeats but makes searching and sorting on the field quicker, which is useful for fields like Surname that you look things up by often but that naturally repeat.

Unique across several fields

Plenty of real duplicates only show up as a combination. Two people can share a surname, and two can share a date of birth, but the same surname, first name and date of birth together is very likely the same person. An invoice number may only need to be unique within one customer's account. A booking table might allow many bookings per room and many per date, but only one for each room on each date.

For these, open the Rules dialog and add an index covering the fields together, marked unique. Base then refuses any record whose combination of values is already taken, while still allowing each value to repeat on its own.

Cleaning up duplicates that are already there

If the table already holds duplicates, deal with them before adding the rule. Sort the datasheet on the field so repeats sit next to each other, or use the search box to look for one value at a time. For a bigger table, the visual query builder can group by the fields and count the records in each group; any count above one is a duplicate to look at.

Decide which copy to keep, merge anything useful from the other, and delete the spare. If other tables look up the record you are deleting, the lookup's delete rule decides what happens, so check that before you remove anything that has orders or bookings hanging off it.

Duplicates arriving from imports

Imports are where most duplicates come from: last year's spreadsheet imported twice, or two lists from different people. When you import a CSV or Excel file into an existing table, rows that break the table's rules are skipped and listed instead of being added. A unique field or unique index therefore filters out the repeats during the import and shows you which rows they were.

Choosing what counts as the same

Be careful not to make things unique that aren't. Names are the obvious trap. A phone number can be shared by a household. An email address may be shared too, in a family or a small business. Choose the field or combination that really identifies one thing in your world, and leave the rest free.

It also helps to keep values consistent before you rely on unique checks. An input mask makes sure postcodes and phone numbers are always written the same way, so SW1A 1AA and sw1a1aa can't sit side by side as two different values.

When you export the database as a website, Base by Zaweba carries the unique keys into the MySQL or MariaDB tables, so the browser version refuses duplicates in exactly the same way.