How can I enforce compound uniqueness in MySQL?

database-design, mysql, sql

Solution

Add a UNIQUE key to your table definition:

Table (
  id char(36) primary key,
  fieldA varChar(12) not null,
  fieldB varChar(36) not null,
  UNIQUE fieldA_fieldB (fieldA, fieldB)
)

Problem

I have run into a situation where I want to ensure that a compound element of a table is unique. For example: ``` Table ( id char(36) primary key, fieldA varChar(12) not null, fieldB varChar(36) not null ) ``` I don't want fieldA and fieldB to be a compound primary key, since they change frequently, and 'id' is used as a reference throughout the system. fieldA and fieldB are not unique in and of themselves, but their combinations need to be unique. So for example, {{1, Matt, Jones}, {2, David, Jones}, {3, Matt, Smith}} would be valid data, but {{1, Matt, Jones}, {2, Matt, Jones}} would not be.

Original source