Composite primary keys in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a composite primary key in a database?

A composite primary key is a primary key made up of two or more columns that together uniquely identify each row in a table.

beginner

Why would you use a composite primary key instead of a single column primary key?

You use a composite primary key when no single column can uniquely identify a row, but a combination of columns can.

beginner

How do you define a composite primary key in SQL?

You list multiple columns inside the PRIMARY KEY clause, separated by commas, like: PRIMARY KEY (column1, column2).

intermediate

Can a composite primary key contain NULL values in any of its columns?

No, all columns in a composite primary key must have NOT NULL values to ensure uniqueness.

beginner

Example: What is the composite primary key in this table definition? CREATE TABLE Enrollment ( student_id INT, course_id INT, enrollment_date DATE, PRIMARY KEY (student_id, course_id) );

The composite primary key is the combination of student_id and course_id columns. Together, they uniquely identify each enrollment record.

What does a composite primary key consist of?

A Multiple columns combined to uniquely identify a row

B A single column with multiple values

C A foreign key referencing another table

D A column that allows NULL values

A composite primary key uses multiple columns together to uniquely identify each row.

Which SQL syntax correctly defines a composite primary key?

A PRIMARY KEY column1, column2

B PRIMARY KEY (column1, column2)

C PRIMARY KEY column1 AND column2

D PRIMARY KEY column1 OR column2

The correct syntax uses parentheses around the columns separated by commas.

Can any column in a composite primary key contain NULL values?

A No, all columns must be NOT NULL

B Only one column can have NULL

C It depends on the database

D Yes, NULLs are allowed

All columns in a primary key must be NOT NULL to guarantee uniqueness.

Why might you choose a composite primary key?

A To allow duplicate rows

B To allow NULL values in keys

C To speed up queries

D Because no single column uniquely identifies rows

Composite keys are used when a single column is not enough to uniquely identify rows.

In the table Enrollment(student_id, course_id), which is the best primary key?

A student_id only

B course_id only

C student_id and course_id together

D enrollment_date

The combination of student_id and course_id uniquely identifies each enrollment.

Explain what a composite primary key is and why it is useful.

Think about how two or more columns together can identify a record uniquely.

Describe how to define a composite primary key in SQL with an example.

Remember the parentheses and commas between column names.