How can I enforce compound uniqueness in MySQL?

Question

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.

Answer 1

You can add an UNIQUE key constraint on the two fields:

ALTER TABLE `table_name` ADD UNIQUE (
`fieldA`,
`fieldB`
);

Answer 2

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)
)

Answer 3

ALTER Table ADD UNIQUE ( fieldA, fieldB );