I'd just like to clarify something pretty simple regarding single and composite UNIQUE constraints that I can't find a clear answer to.

Assume I have the following table:

  customer_id INT NOT NULL UNIQUE,

Would it be safe to say that adding the following constraint:

UNIQUE(customer_id, name)

would be redundant and offer no benefit under any circumstance? In other words, is it useless to create a composite UNIQUE constraint that includes a column which is already UNIQUE?

This appears to make logical sense, but as I stated, I couldn't find an answer confirming it.

  • 1
    Seems so, although it could help in DBMSs which do not offer INCLUDE on indexes. Out of curiosity, why is there both id and customer_id if they are both unique? Shouldn't they be the same column? Commented Sep 5, 2023 at 22:08
  • Hi, and welcome to dba.se! UNIQUE(name) should do it - id is just a surrogate!
    – Vérace
    Commented Sep 6, 2023 at 1:01
  • Thanks guys! @Charlieface This is just a really simple example I based off my situation (I'm pulling from multiple sources where the same entities are represented by different IDs) Commented Sep 6, 2023 at 3:00

2 Answers 2


Assuming your example is valid,above constraint is not redundant.

customer_id is unique on its own way.

But as per business requirement customerid+name should also be unique.

Say as per biz req. Customer_id is in specific format or it source is some other system.

ID is auto increment and it is use by database internally. So id and customer_id both will be need and both there purpose and functionality are different.

As per biz req. customer_id+name(some code) should be unique, and you explicitly define it unique as you do not want to violate biz rule.

So given constraint is not redundant.

Constraint has its own demerit i.e. constraint is evaluated each when there will be DML operation on table.

So please come with exact table and requirement, so that we can discuss if both constraint is require or how to achieve same thing with single constraint.

  • perhaps I should've specified my definition of redundant - I could care less about "business rules" as you put it if the only benefit obtained is purely semantic, and has no effect on the actual constraint and/or indexing (not to mention the potential downsides regarding memory / disk space of unnecessary indexes). My question is purely in regards to performance and memory / disk footprint. Commented Sep 7, 2023 at 17:41

Are you asking about satisfying a query like this?

select col1, col2, col3 from person where customer_id = 785

If customer_id is the leading component in two indexes, the query optimizer will have a choice as to which one to use. My guess is that it would probably pick the index which has the lowest number of physical pages.

If the query was:

select col1, col2, col3 from person where customer_id between 1000000 and 3000000 order by name 

I think it would probably pick the second one. HTH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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