Unique constraint based on associated column value

Question

I have the following (simplified) data structure:

Property                    Product
-----------   n <----> 1    -------
product_id                  manufacturer
type                        name
value

Where the products table might look like this

| name         |
|--------------|
| iPad 2       |
| iPhone 6     |

and the associated properties might be

| product_id | type        | value |
|----------------------------------|
|      1     | RAM         | 16GB  |
|      1     | CPU         | A11   |
|      1     | Screen Size | 10"   |
|      2     | Cellular    | yes   |
|      2     | Screen Size | 5.5"  |

Is it possible, in MySQL, to create a constraint, so that every product can have each property type at most once (e.g., a product can't have multiple Screen Sizes associated. As far as I could understand the MySQL docs, constraints only work on one relation, but not across relations.

Also, consider storing types in a separate table

Strawberry
– Strawberry

2019-05-25 10:30:24 +00:00
Commented May 25, 2019 at 10:30 — Strawberry
– Strawberry, Commented May 25, 2019 at 10:30
@Strawberry true, my example isn't normalized

Lukas_Skywalker
– Lukas_Skywalker

2019-05-25 13:23:08 +00:00
Commented May 25, 2019 at 13:23 — Lukas_Skywalker
– Lukas_Skywalker, Commented May 25, 2019 at 13:23

Lukas_Skywalker · Accepted Answer · 2019-05-25 10:13:05Z

3

I realized the solution immediately after posting this question. The constraint can be placed on the properties relation, so that each pair of product_id, type can only exist once.

answered May 25, 2019 at 10:13

Lukas_Skywalker

2,0701 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unique constraint based on associated column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related