Is a Composite UNIQUE Constraint On an Already UNIQUE Column Redundant?

Question

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:

CREATE TABLE person (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL UNIQUE,
  name VARCHAR(20) NOT NULL
  ...
)

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.

Answer 1

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.

Answer 2

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