Primary key

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which field would make the best primary key for a Students table?

A StudentID

B FirstName

2. True or false: A primary key value can be repeated in two different records of the same table.

A False

B True

3. Why is a person's name a poor primary key?

A Names are too short to store

B Two people can have the same name

4. What does auto-numbering do for a primary key field?

A Gives each new record the next unique number

B Lets you type any number, even a repeated one

5. Which table design uses a primary key correctly?

A Students(StudentID, Name, Year) with Year as the key

B Students(StudentID, Name, Year) with StudentID as the key

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1StudentID INTEGER PRIMARY KEY		AName is a poor primary key because two students can have the same name. Use a unique ID number such as StudentID instead.
2INSERT INTO Students VALUES (101, 'Mia');		BAdds a record whose ID 101 must not already be used by another record
3Auto-numbering lets the database give each new record the next unique ID automatically		CTwo records share the ID 101, so the primary key is not unique. Each record needs its own ID, such as 102 for Jack.
4A primary key is a field that is unique for every record in a table		DStudentID INTEGER PRIMARY KEY AUTOINCREMENT
5Name TEXT PRIMARY KEY		ECREATE TABLE Students (StudentID INTEGER PRIMARY KEY, Name TEXT);
6Students: ID 101 Mia, ID 101 Jack		FMakes the StudentID field the primary key, so every record has its own unique ID number

Definitions

PRIMARY KEY

Fill in the Blanks

Complete each line of code by filling in the missing parts.

CREATE TABLE Pet (PetID INT ___________);

CREATE TABLE Pet ( PetID INTEGER PRIMARY KEY _____________, Name TEXT);

CREATE TABLE Student (StudentID INT, Name TEXT, PRIMARY KEY (_________));

SELECT * FROM Pet WHERE _____ = 7;

CREATE TABLE Game ( GameID ___ PRIMARY KEY, Title TEXT);

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A CREATE TABLE Student (

B StudentID INT PRIMARY,

C Name TEXT,

D Year INT

E );

Question 2

A CREATE TABLE Pet (

B PetID TEXT AUTO_INCREMENT,

C Name TEXT,

D PRIMARY KEY (PetID)

E );

Question 3

A -- Name is the primary key

B CREATE TABLE Player (

C PlayerID INT PRIMARY KEY,

D Name TEXT

E );

Question 4

A INSERT INTO Student VALUES

B (101, 'Mia', 9),

C (NULL, 'Sam', 9);

Question 5

A CREATE TABLE Student (

B StudentID INT,

C Name TEXT,

D PRIMARY KEY (Name)

E );

Answer Key

Primary key

Section A: Quick Questions

A — StudentID

A — False

B — Two people can have the same name

A — Gives each new record the next unique number

B — Students(StudentID, Name, Year) with StudentID as the key

Section B: Match the Pairs

StudentID INTEGER PRIMARY KEY → Makes the StudentID field the primary key, so every record has its own unique ID number

INSERT INTO Students VALUES (101, 'Mia'); → Adds a record whose ID 101 must not already be used by another record

Auto-numbering lets the database give each new record the next unique ID automatically → StudentID INTEGER PRIMARY KEY AUTOINCREMENT

A primary key is a field that is unique for every record in a table → CREATE TABLE Students (StudentID INTEGER PRIMARY KEY, Name TEXT);

Name TEXT PRIMARY KEY → Name is a poor primary key because two students can have the same name. Use a unique ID number such as StudentID instead.

Students: ID 101 Mia, ID 101 Jack → Two records share the ID 101, so the primary key is not unique. Each record needs its own ID, such as 102 for Jack.

Section C: Definitions

PRIMARY KEY — A field whose value is unique for every record in a table, such as a student ID number, so each record can be identified exactly. Names make poor keys because they can repeat.

Section D: Fill in the Blanks

CREATE TABLE Pet (PetID INT PRIMARY KEY);

CREATE TABLE Pet (
  PetID INTEGER PRIMARY KEY AUTOINCREMENT,
  Name TEXT);

CREATE TABLE Student (StudentID INT,
Name TEXT, PRIMARY KEY (StudentID));

SELECT * FROM Pet WHERE PetID = 7;

CREATE TABLE Game (
  GameID INT PRIMARY KEY, Title TEXT);

Section E: Spot the Error

B — The keyword is PRIMARY KEY, both words. Without KEY, the database does not mark StudentID as the unique identifier. It should be: StudentID INT PRIMARY KEY,

B — Auto-numbering counts up 1, 2, 3 and so on, so the field needs a number type. It should be INT, not TEXT.

A — Names are poor keys because two players can share a name. The unique ID number, PlayerID, is the primary key, as line 3 shows.

C — A primary key can never be empty (NULL), because every record needs an ID to be identified. Sam needs a unique ID such as 102.

D — Names can repeat, so Name cannot identify a record exactly. The primary key should be the unique field: PRIMARY KEY (StudentID).