UniqueConstraint and CompositeIndex in MySql and other dbs

I’d like to know if these are redundant.

Does a unique constraint automatically acts like a composite index making the CompositeIndex redundant in MySql? And if so is this the case for all db’s supported by Ormlite?

[UniqueConstraint(nameof(CustomerId), nameof(Day), nameof(Server)]

[CompositeIndex(nameof(CustomerId), nameof(Day), nameof(Server)]

Thanks

You’d need to make the composite index unique in order for it to also enforce uniqueness, e.g:

[CompositeIndex(nameof(CustomerId), nameof(Day), nameof(Server), Unique = true)]

Then you wouldn’t need your Unique Constraint.

Just to clarify, does this mean that:

[UniqueConstraint(nameof(CustomerId), nameof(Day), nameof(Server)]

and
[CompositeIndex(nameof(CustomerId), nameof(Day), nameof(Server), Unique = true)]

Are equivalent in terms of functionality and are fully interchangeable?

Or it’s better to use the CompositeIndex because the UniqueConstraint just check uniqueness and there’s no related index or it doesn’t act like an index at all?

From what I can find online it seems that there’s an index on a unique constraint (in MySql at least) but I got this on stackoverflow and the multiples answers felt more like opinions (even the accepted answer) than a definitive answer.

No, have a look at the RDBMS Create Table SQL they generate by enabling SQL logging:

OrmLiteUtils.PrintSql();

[UniqueConstraint] creates a unique RDBMS constraint, whilst [CompositeIndex] creates a composite RDBMS index, adding Unique=true generates a UNIQUE composite index. You’d decide which to use based on whether or not your System would benefit from the existence of this specific composite index, if you’re unsure don’t create it.

These attributes do not have any affect of OrmLite’s behavior at runtime, i.e. they’re only used when OrmLite creates the table.

Excellent. Thank you for the clarification!

1 Like