#Creating unique keys in SQLite3

##Why create a unique key?

Say you want to ensure that certain values are never duplicated across rows in a given table-- e.g., voters can vote in many polls but they can only vote in one poll once. In the votes table, a unique key would be applied to the combination of voter_id and poll_id.

Or, say you have a sections table that represents sections of university courses. A section has a teacher, a teacher can teach many sections, but obviously a teacher cannot teach multiple sections that are held during the same day/time interval. So, you'll need a unique key on teacher_id, days_held and time_block.

##Create your SQL table

From the console:

$ touch sandbox.db
$ sqlite3 sandbox.db

In SQLite3, your table without the unique key might look like this:

CREATE TABLE sections (
id INTEGER PRIMARY KEY AUTOINCREMENT,
course_id INTEGER,
teacher_id INTEGER,
days_held VARCHAR(64),
time_block VARCHAR(64)
);

Note that there are not NOT NULL constraints defined because I'm lazy and it's irrelevant to understanding unique keys + SQL.

To add a unique key, just append the code after the last column definition, with the columns combination you want to unique-ify specified in parentheses-- e.g.:

CREATE TABLE sections (
id INTEGER PRIMARY KEY AUTOINCREMENT,
course_id INTEGER,
teacher_id INTEGER,
days_held VARCHAR(64),
time_block VARCHAR(64),
UNIQUE (teacher_id, days_held, time_block)
);

You can verify that this is working by trying to insert rows where the values in those columns are duplicated.

First:

INSERT INTO sections
(course_id, teacher_id, days_held, time_block)
VALUES
(1,1,"MWF","08:00-10:00"),
(2,2,"MWF","08:00-10:00"),
(1,1,"TTH","08:00-10:00");

This executes without errors, and SELECT * FROM sections; will return three rows of data.

Now then the duplicate attempt:

INSERT INTO sections
(course_id, teacher_id, days_held, time_block)
VALUES
(1,1,"MWF","08:00-10:00");

This throws an error: Error: columns teacher_id, days_held, time_block are not unique, and SELECT * FROM sections; will still only return three rows of data.

But

INSERT INTO sections
(course_id, teacher_id, days_held, time_block)
VALUES
(1,2,"MWF","08:00-10:00"),
(1,1,"MWF","10:00-12:00");

will execute without any errors.

##Get fancy!

###Multiple unique keys for different uniqueness combos

Maybe the university also wants to ensure that no course is offered more than once per day grouping (MWF, TTH). This would require a unique key to be applied to course_id and days_held.

DROP TABLE sections;

CREATE TABLE sections (
id INTEGER PRIMARY KEY AUTOINCREMENT,
course_id INTEGER,
teacher_id INTEGER,
days_held VARCHAR(64),
time_block VARCHAR(64),
UNIQUE (teacher_id, days_held, time_block),
UNIQUE (course_id, days_held)
);

INSERT INTO sections
(course_id, teacher_id, days_held, time_block)
VALUES
(1,1,"MWF","08:00-10:00"),
(2,2,"MWF","08:00-10:00"),
(1,1,"TTH","08:00-10:00");

and test the new unique key:

INSERT INTO sections
(course_id, teacher_id, days_held, time_block)
VALUES
(1,3,"MWF","10:00-12:00");

This should return the error: Error: columns course_id, days_held are not unique, but

INSERT INTO sections
(course_id, teacher_id, days_held, time_block)
VALUES
(2,1,"TTH","10:00-12:00");

will execute without errors.

###Why do I have to drop the table? Why can't I just alter the table and add a new unique constraint?

I have no idea. I've Googled this for pages and you SHOULD be able to execute something like

ALTER TABLE sections
ADD CONSTRAINT course_days UNIQUE(course_id, days_held);

but I keep getting back Error: near "CONSTRAINT": syntax error

So, yeah. If you figure this out, let me know.