SQL: What is does the UNIQUE mean when creating a primary key

Question

I've got a table called students:

+------------+------------+-----------+---------------------+---------------------+ | student_id | first_name | surname | email | reg_date | +------------+------------+-----------+---------------------+---------------------+ | 1 | Emily | Jackson | [email protected] | 2012-10-14 11:14:13 | | 2 | Daniel | ALexander | [email protected] | 2014-08-19 08:08:23 | | 3 | Sarah | Bell | [email protected] | 1998-07-04 13:16:32 | | 4 | Alex | Harte | [email protected] | 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

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 [email protected] as their address. — Marc B
– Marc B, Commented Jun 2, 2016 at 18:27
Possible duplicate of Difference between Key, Primary Key, Unique Key and Index in MySQL — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jun 2, 2016 at 18:27
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
– chungtinhlakho, Commented Jun 2, 2016 at 18:29

Cee McSharpface · Accepted Answer · 2016-06-02 18:37:30Z

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

Alex · Accepted Answer · 2016-06-02 18:51:25Z

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

Collectives™ on Stack Overflow

SQL: What is does the UNIQUE mean when creating a primary key

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related