Apply the unique constraint to combinations of columns in SQL

Question

Hi, I have a table called product_attributes in a MySQL Database. It consists of a product SKU (foreign key), an attribute label and an attribute value. I need to ensure that combinations of SKU and attribute labels are unique.

EXAMPLE -

inserting this would be legal

{001, 'weight', '100 lbs'}
{001, 'color', 'blue'}
{002, 'weight', '200 lbs'}

This would be illegal

{001, 'weight', '100 lbs'}
{001, 'weight', '200lbs' }

---

-How can enforce this constraint?
-Do I have to do this in my server-side language or can I configure my database to enforce this?
-What is this type of constraint called?

Answer 1

You can either make ProductSKU and AttributeLabel a composite PK, or put a unique constraint on them. Either will achieve your purpose. You may wish to use a surrogate key for convenience (or because your model layer requires it), in which case the unique constraint is the way to go.

Typically you will still need code to handle exceptions in your applications, regardless of what you do with the database.

Answer 2

Haven't tested this but the DDL (Data Definition Language) for adding a Unique key is

ALTER TABLE foo
ADD CONSTRAINT UniqueSkuAttribKey UNIQUE (SKU, Attribute)

Primary key is

ALTER TABLE foo
ADD CONSTRAINT PrimaryFooKey Primary (SKU, Attribute)

Answer 3

Use a unique constraint on the two rows

ALTER TABLE product_attributes ADD CONSTRAINT unique_sku_attribute UNIQUE (sku, attribute);

edit: reworded from unique index to unique constraint. oops