Can I add a unique constraint to a combination of columns in MySQL?

Question

I have a MySQL table used for linking records in two other tables where the primary key is auto-numbered, and the two other columns are tied to the primary keys of the other two tables. I need to constrain this table so that it won't accept duplicate records, ignoring the auto-number. Can I do this?

To simplify the scenario (ignoring the unnecessary info about the other tables), imagine a table of people's names, with an auto-id column, a first name column, and a last name column. Could a constraint be added to prevent the same first and last name combination from being entered twice?

Answer 1

Create a compound index on the columns tied to the other tables, and mark it unique.

If you have, say, tables A and B add an index to your third table like this:

ALTER TABLE `thirdTable` 
ADD UNIQUE INDEX `UQ_AB` (`id_A` ASC, `id_B` ASC);