0

I've got a table called students:

+------------+------------+-----------+---------------------+---------------------+
| student_id | first_name | surname   | email               | reg_date            |  
+------------+------------+-----------+---------------------+---------------------+
|          1 | Emily      | Jackson   | emilyj@gmail.com    | 2012-10-14 11:14:13 |
|          2 | Daniel     | ALexander | daniela@hotmail.com | 2014-08-19 08:08:23 |
|          3 | Sarah      | Bell      | sbell@gmail.com     | 1998-07-04 13:16:32 |
|          4 | Alex       | Harte     | AHarte@hotmail.com  | 1982-06-14 00:00:00 |
+------------+------------+-----------+---------------------+---------------------+

When creating the table:

CREATE TABLE students(
-> student_id INT NOT NULL AUTO_INCREMENT,
-> first_name VARCHAR(30) NOT NULL,
-> surname VARCHAR(50) NOT NULL,
-> email VARCHAR(200) NOT NULL,
-> reg_date DATETIME NOT NULL,
-> PRIMARY KEY (student_id),
-> UNIQUE (email));

What does the 'UNIQUE (email)' mean? Does it mean if the primary key isn't unique, look at the email to see if that's unique instead? Or something different?

Thanks

Dovahkiin
  • 247
  • 1
  • 4
  • 16
  • 4
    it means that any particular value in that field can appear only ONCE in the entire table. that means you can't have two+ users with `president@whitehouse.gov` as their address. – Marc B Jun 02 '16 at 18:27
  • Possible duplicate of [Difference between Key, Primary Key, Unique Key and Index in MySQL](http://stackoverflow.com/questions/3844899/difference-between-key-primary-key-unique-key-and-index-in-mysql) – Juan Carlos Oropeza Jun 02 '16 at 18:27
  • 1
    @Marc, you should post that as an answer. – Brad Jun 02 '16 at 18:28
  • In this case, shouldn't the email just be primary key? your student id will always be unique because it's auto_increment. Just a thought. – chungtinhlakho Jun 02 '16 at 18:29

2 Answers2

2

The UNIQUE keyword creates a unique constraint on the columns that are mentioned in its argument list (in this case, email). It does not interfere with the primary key. It will enforce unique values on the email column, that is, fail with an exception when a row is about to be INSERTed (or UPDATEd) that would collide with an existing row.

A primary key (by default) implies a unique constraint. So as you designate student_id as your primary key, the RDBMS will also automatically maintain unique values in that column for you.

Further reading: http://www.w3schools.com/sql/sql_unique.asp

Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
1

It allows the engine to use it as an index in queries and enforces it to be unique when a record/s are inserted/updated; throwing a violation of a unique key constraint when an already existing email is inserted/updated.

Example: http://sqlfiddle.com/#!9/7a0aee

More Information: http://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html

Alex
  • 34,899
  • 5
  • 77
  • 90