1

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 at 9:07
2

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:

PRIMARY KEY(id),
UNIQUE(x)

is like

PRIMARY KEY(id),
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.

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.