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&gt; 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&gt; INSERT INTO Example (name) VALUES('Peter Parker');
ERROR 1062 (23000): Duplicate entry 'Peter Parker' for key 'uix_name_example'
mysql&gt; 


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&gt; INSERT INTO Example VALUES(1, 'Peter Parker');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql&gt;


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&gt; INSERT INTO Example2 VALUES(1, 'Peter Parker');
Query OK, 1 row affected (0.12 sec)
mysql&gt; 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&gt; INSERT INTO Example2 VALUES(1, 'Mary Hanlon');
Query OK, 1 row affected (0.06 sec)

mysql&gt; 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&gt; INSERT INTO Example2 VALUES(1, 'Peter Parker');
ERROR 1062 (23000): Duplicate entry '1-Peter Parker' for key 'uix_example'
mysql&gt; 


Hopefully this resolves your question.