Search code examples
mysqlduplicatesunique

Limit unique values in MySQL per key

I have these two tables

First table: 

Name: Mines   
First column: ID of mine (primary)   
Second column: Name of mine (VARCHAR 50)

Second table: 

Name: OresInMine   
First column: ID of entry (primary)   
Second column: ID of mine   
Third column: Ore name (VARCHAR 30)

I need to avoid duplicate entries in third column of table 2 for each mine ID separately.

Example of what I want to avoid follows:

Table Mines   
1 - Dark Mine   
2 - Abandoned Mine   

Table OresInMine   
1 - 1 - Iron   
2 - 1 - Coal   
3 - 2 - Iron   
4 - 2 - Copper   
5 - 2 - Coal   
**6 - 1 - Coal** (There is already Coal in mine with ID 1)

I want from MySQL to block inserts which could cause this duplicate for each mine.

Solution

  • You need to make a convination of the column 2 and 3 (ID of mine and Ore name) a UNIQUE KEY in your second table OresInMine, so you can have one 1-Iron and one 1-coal and also avoid the duplication issue.

    Best regards, Juan A. Rubio