Constraint for a unique column if other column has same value

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

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.

StackExchange Database Administrators Q#135544, answer score: 5