I'm aware that INNODB includes an implicit primary key in an index such as:

INDEX (non_primary_column)

Does it apply to the UNIQUE constraint too?

UNIQUE (other_primary_column) -- equivalent to INDEX (other_primary_column, pkey)?

… and FOREIGN constraint too?

FOREIGN KEY (some_other_column) -- equivalent to INDEX (some_other_column, pkey)?
  • Any index contains clustered (it may be NOT primary) index expression hiddenly. Constraint is NOT an index (it is a rule), and so it cannot contain any expression. – Akina Nov 14 '19 at 9:07

Yes and no.

Yes in that the PK column(s) are tacked onto each UNIQUE index and the INDEX implicitly generated by each FOREIGN KEY.

No in that the PK column(s) are not part of the uniqueness constraint implied by UNIQUE.

For example:


is like

INDEX(x,id) and a uniqueness constraint on only `(x)`.

FOREIGN KEY(y) will generate INDEX(y) unless it finds an existing index starting with y.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.