1

I'm wondering if it's possible to have a column be unique for each value of another column in MySQL. What I'm wanting to have is the same functionality as what a GROUP BY would do.

Here's the table:

CREATE TABLE Example (
    id INT,
    name VARCHAR(100) NOT NULL
);

The data would be similar to this:

INSERT INTO Example (id, name) VALUES(1, 'Peter Parker');

The following would work:

INSERT INTO Example (id, name) VALUES(1, 'Bruce Wayne');
INSERT INTO Example (id, name) VALUES(2, 'Peter Parker');

The following would not work (as there's the same name with that id):

INSERT INTO Example (id, name) VALUES(1, 'Peter Parker');

Hopefully this can help clear things up for what I want:

  • There can be multiple rows with the same ID
  • There can be multiple rows with the same name
  • There cannot be multiple rows with the same ID and name
3

If I've understood you correctly, what you want is something like this:

CREATE TABLE Example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uix_name_example (name)
);

The id field is the PRIMARY KEY and its value is taken care of by the server. A UNIQUE KEY on name ensures no duplicates are allowed - and NOT NULL ensures (hopefully) meaningful values.

INSERT INTO Example (name) VALUES ('Peter Parker'); -- specify name, but not id - it's automatic
INSERT INTO Example (name) VALUES ('Billy Joe McAlister');
INSERT INTO Example (name) VALUES ('Bill Clinton');

Check your table:

mysql> SELECT * FROM Example;
+----+---------------------+
| id | name                |
+----+---------------------+
|  3 | Bill Clinton        |
|  2 | Billy Joe McAlister |
|  1 | Peter Parker        |
+----+---------------------+
3 rows in set (0.03 sec)

A subsequent attempt to INSERT the same name (Peter Parker) fails.

mysql> INSERT INTO Example (name) VALUES('Peter Parker');
ERROR 1062 (23000): Duplicate entry 'Peter Parker' for key 'uix_name_example'
mysql> 

If you want to allow people with the same name, but different ids, then you just leave out the UNIQUE KEY uix_name_example (name) line in the table definition.

mysql> INSERT INTO Example VALUES(1, 'Peter Parker');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>

If this isn't a satisfactory explanation, please expand your question.


(EDIT in response to OP's comment and clarification)

What you want is a UNIQUE KEY (or INDEX) on the combined id and name fields - also known as a composite index (or key). The syntax for this is as follows:

CREATE TABLE Example2 (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uix_example (id, name)
);

Duplicate ids with different names allowed to be inserted.

mysql> INSERT INTO Example2 VALUES(1, 'Peter Parker');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO Example2 VALUES(1, 'Billy Power');
Query OK, 1 row affected (0.08 sec)

Same name allowed to be inserted with different ids

mysql> INSERT INTO Example2 VALUES(1, 'Mary Hanlon');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO Example2 VALUES(2, 'Mary Hanlon');
Query OK, 1 row affected (0.06 sec)

But, what is not allowed is to insert same id and same name.

mysql> INSERT INTO Example2 VALUES(1, 'Peter Parker');
ERROR 1062 (23000): Duplicate entry '1-Peter Parker' for key 'uix_example'
mysql> 

Hopefully this resolves your question.

2
  • No, that's not what I want. I added points to explain the rules I want in the question above.
    – ub3rst4r
    Apr 17 '16 at 16:26
  • Check out my edit where I added supplementary information about composite UNIQUE INDEXes (or KEYs).
    – Vérace
    Apr 17 '16 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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