Composite primary keys in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a composite primary key on columns 'order_id' and 'product_id'.

SQL

CREATE TABLE order_details (order_id INT, product_id INT, quantity INT, PRIMARY KEY ([1]));

Drag options to blanks, or click blank then click option'

Aorder_id, product_id

Bproduct_id

Corder_id

Dquantity

Attempts:

3 left

💡 Hint

Common Mistakes

Using only one column as primary key instead of both.

Including columns that are not part of the key.

The composite primary key must include both 'order_id' and 'product_id' together to uniquely identify each row.

2fill in blank

medium

Complete the code to add a composite primary key constraint named 'pk_enrollment' on 'student_id' and 'course_id'.

SQL

ALTER TABLE enrollments ADD CONSTRAINT pk_enrollment [1] (student_id, course_id);

Drag options to blanks, or click blank then click option'

ACHECK

BPRIMARY KEY

CUNIQUE

DFOREIGN KEY

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'FOREIGN KEY' instead of 'PRIMARY KEY'.

Using 'UNIQUE' which allows NULLs.

The constraint to define a composite primary key is 'PRIMARY KEY'.

3fill in blank

hard

Fix the error in the composite primary key definition by completing the code.

SQL

CREATE TABLE attendance (student_id INT, class_id INT, date DATE, PRIMARY KEY [1]);

Drag options to blanks, or click blank then click option'

A{student_id, class_id}

B[student_id, class_id]

Cstudent_id, class_id

D(student_id, class_id)

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting parentheses causes syntax errors.

Using square brackets or curly braces instead of parentheses.

The columns in a PRIMARY KEY must be enclosed in parentheses.

4fill in blank

hard

Fill both blanks to create a table 'subscriptions' with a composite primary key on 'user_id' and 'service_id'.

SQL

CREATE TABLE subscriptions (user_id INT, service_id INT, start_date DATE, [1] PRIMARY KEY [2]);

Drag options to blanks, or click blank then click option'

ACONSTRAINT sub_pk

BFOREIGN KEY

C(user_id, service_id)

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'FOREIGN KEY' instead of 'PRIMARY KEY'.

Not enclosing columns in parentheses.

Use 'CONSTRAINT sub_pk' to name the primary key, followed by the columns in parentheses.

5fill in blank

hard

Fill all three blanks to define a composite primary key named 'pk_order_item' on 'order_id' and 'item_id' in the 'order_items' table.

SQL

CREATE TABLE order_items (order_id INT, item_id INT, quantity INT, [1] pk_order_item [2] [3] (order_id, item_id));

Drag options to blanks, or click blank then click option'

ACONSTRAINT

BPRIMARY

CKEY

DFOREIGN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'FOREIGN' instead of 'PRIMARY'.

Omitting 'CONSTRAINT' keyword.

The correct syntax is: CONSTRAINT pk_order_item PRIMARY KEY (order_id, item_id).

Practice

1. What is a composite primary key in a database table?

easy

A. A primary key that uses only one column to identify rows.

B. A primary key made up of two or more columns combined to uniquely identify a row.

C. A key that allows duplicate values in the table.

D. A foreign key that references multiple tables.

Solution

1. Step 1: Understand primary key basics

   A primary key uniquely identifies each row in a table.

2. Step 2: Define composite primary key

   A composite primary key uses two or more columns together to ensure uniqueness.

3. Final Answer:

   A primary key made up of two or more columns combined to uniquely identify a row. -> Option B

4. Quick Check:

   Composite primary key = multiple columns [OK]

Hint: Composite keys combine columns to ensure unique rows [OK]

Common Mistakes:

• Thinking a primary key can have duplicates
• Confusing composite key with foreign key
• Assuming composite key uses only one column

2. Which of the following is the correct syntax to define a composite primary key on columns order_id and product_id in SQL?

easy

A. PRIMARY KEY (order_id, product_id)

B. PRIMARY KEY order_id, product_id

C. PRIMARY KEY order_id & product_id

D. PRIMARY KEY (order_id + product_id)

Solution

1. Step 1: Recall SQL syntax for composite keys

   Composite keys are defined by listing columns inside parentheses separated by commas.

2. Step 2: Match correct syntax

   PRIMARY KEY (order_id, product_id) uses parentheses and comma correctly: PRIMARY KEY (order_id, product_id).

3. Final Answer:

   PRIMARY KEY (order_id, product_id) -> Option A

4. Quick Check:

   Composite key syntax uses parentheses and commas [OK]

Hint: Use parentheses and commas for composite keys [OK]

Common Mistakes:

• Omitting parentheses around columns
• Using symbols like & or + incorrectly
• Listing columns without commas

3. Given the table OrderDetails with composite primary key (order_id, product_id), what will this query return?

SELECT * FROM OrderDetails WHERE order_id = 101;

medium

A. No rows because both keys must be specified.

B. Only one row with order_id 101 and any product_id.

C. An error because product_id is missing in WHERE clause.

D. All rows where order_id is 101, regardless of product_id.

Solution

1. Step 1: Understand composite key usage in queries

   Composite keys uniquely identify rows, but queries can filter by any column(s).

2. Step 2: Analyze the query filter

   The query filters only by order_id = 101, so it returns all rows with that order_id regardless of product_id.

3. Final Answer:

   All rows where order_id is 101, regardless of product_id. -> Option D

4. Quick Check:

   Filtering by part of composite key returns matching rows [OK]

Hint: Filtering by part of composite key returns matching rows [OK]

Common Mistakes:

• Assuming missing key column causes error
• Thinking only full composite key filters work
• Expecting only one row when multiple match

4. You try to create a table with this SQL:

CREATE TABLE Enrollment (
 student_id INT,
 course_id INT,
 PRIMARY KEY student_id, course_id
);

What is the problem?

medium

A. Missing parentheses around the composite key columns.

B. student_id and course_id cannot be primary keys.

C. PRIMARY KEY must be declared after all columns.

D. Composite keys require UNIQUE keyword instead.

Solution

1. Step 1: Check syntax for composite primary key

   Composite keys require parentheses around the column list in PRIMARY KEY declaration.

2. Step 2: Identify error in given SQL

   The statement uses PRIMARY KEY student_id, course_id without parentheses, causing syntax error.

3. Final Answer:

   Missing parentheses around the composite key columns. -> Option A

4. Quick Check:

   Composite keys need parentheses [OK]

Hint: Always use parentheses for composite primary keys [OK]

Common Mistakes:

• Omitting parentheses in PRIMARY KEY clause
• Confusing primary key with unique constraint
• Placing PRIMARY KEY before column definitions

5. You have a table Attendance with columns student_id, class_date, and session. You want to ensure each student can only have one attendance record per class date and session. Which composite primary key should you define?

hard

A. (class_date, session)

B. (student_id, class_date)

C. (student_id, class_date, session)

D. (student_id, session)

Solution

1. Step 1: Understand uniqueness requirement

   Each student must have only one record per class date and session, so all three columns combined must be unique.

2. Step 2: Choose composite key covering all uniqueness factors

   Composite key must include student_id, class_date, and session to enforce this rule.

3. Final Answer:

   (student_id, class_date, session) -> Option C

4. Quick Check:

   Composite key covers all uniqueness columns [OK]

Hint: Include all columns that define uniqueness in composite key [OK]

Common Mistakes:

• Leaving out session or class_date from key
• Using only two columns when three needed
• Confusing foreign keys with primary keys