We often want the values of a database table column to be unique. For example, consider the following table called students:

id	given_name	family_name
451834	Mary	Smith
134922	Emma	Lin
652331	Lucas	Smalls
820980	Billy	Homes

This table contains information about college students. The students are uniquely identified by the student ID number. Thus, students who have the same name can still be distinguished by their ID numbers. When a new student enrolls, the school issues a new, unused student ID number to keep the IDs unique.

We can instruct serve_db to keep values of a table column unique by adding a constraint, or key, to the table. Here is an example:

ALTER TABLE students ADD PRIMARY KEY (id)

This statement tells serve_db to create a primary key on column id of the students table. After the primary key is created, serve_db will refuse to add a new row to the table or update an existing row if the new row ID already exists in the table.

Foreign Key

We can also create foreign keys on table columns. The values in the foriegn key column must exist in the referenced table column.

For exmaple, consider the following table called enrollments:

course_id	student_id	year	grade
CS101	451834	2015	A
CS101	134922	2016	B
MATH152	652331	2017	A
MATH152	134922	2017	C
MATH152	820980	2016	A

This table contains student enrollment information. For example, a student with ID 451834 enrolled in a course called CS101 and received an A. The values in the student_id column must exist in the id column of the students table. To enforce this constraint, we can create a foreign key as follows:

ALTER TABLE enrollments ADD FOREIGN KEY (student_id) REFERENCES students(id)

After the foreign key is created, serve_db will refuse to add a new row to the enrollments table or update an existing row if the student ID of the new row does not exist in the students database.