Primary and Candidate Keys in SQL

• Possibly many candidate keys (specified using UNIQUE), one of which is chosen as the primary key.

CREATE TABLE Enrolled

(sid CHAR(20)

cid CHAR(20),

grade CHAR(2),

PRIMARY KEY (sid,cid) )

• “For a given student and course, there is a single grade.” vs. “Students can take only one course, and receive a single grade for that course; further, no two students in a course receive the same grade.”

• Used carelessly, an IC can prevent the storage of database instances that arise in practice!

CREATE TABLE Enrolled

(sid CHAR(20)

cid CHAR(20),

grade CHAR(2),

PRIMARY KEY (sid),

UNIQUE (cid, grade) )

Previous slide

Next slide

Back to first slide

View graphic version