#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.
same here