Test Your MySQL Foreign Key Expertise with Our SQL Quiz

Progress

True / False

foreign key in MySQL can reference a primary key in another table.

True False

MySQL, a foreign key is used to link two tables together. It refers to the primary key in another table, establishing a relationship between the tables.

True / False

MySQL, a foreign key can only reference columns in the same table.

True False

foreign key in MySQL is designed to reference columns in another table, not the same table.

True / False

You cannot create a foreign key constraint if the referenced column does not have a unique index in MySQL.

True False

MySQL requires that the referenced column must be indexed, typically as a primary key or unique key, for a foreign key constraint to be created.

True / False

MySQL, a table can have multiple foreign key constraints.

True False

MySQL allows a table to have multiple foreign key constraints, each referencing different tables or different columns within the same table.

True / False

Dropping a referenced table automatically removes the foreign key constraints in MySQL.

True False

MySQL, you must first drop the foreign key constraints before dropping the referenced table to avoid errors.

True / False

MySQL supports cascading updates and deletes for foreign key constraints.

True False

MySQL supports cascading operations, such as ON DELETE CASCADE and ON UPDATE CASCADE, which automatically propagate changes to foreign key references.

True / False

MySQL, foreign key constraints can be temporarily disabled.

True False

You can temporarily disable foreign key constraints in MySQL by setting FOREIGN_KEY_CHECKS = 0

True / False

Foreign key constraints in MySQL cannot be defined in InnoDB tables.

True False

MySQL supports foreign key constraints primarily in InnoDB tables, which is a transactional storage engine.

True / False

MySQL, the REFERENCES keyword is mandatory when defining a foreign key constraint.

True False

When defining a foreign key constraint in MySQL, the REFERENCES keyword is used to specify the table and column being referenced.

Q10

True / False

MySQL's foreign key constraints ensure that the referenced data remains consistent, and it prevents operations that would result in an invalid state.

True False

MySQL uses foreign key constraints to enforce referential integrity, ensuring that relationships between tables remain consistent and valid.

Q11

Single Choice

Which SQL keyword is used to define a foreign key in MySQL?

PRIMARY KEY FOREIGN KEY UNIQUECHECK

In MySQL, the FOREIGN KEY keyword is used to define a foreign key constraint that links one table to another.

Q12

Single Choice

What is the purpose of a foreign key in MySQL?

To enforce data integrity between tables To store unique values To index columnsTo define the primary key of a table

A foreign key in MySQL is used to link two tables together and ensure that the data in one table corresponds to the data in another table, thus enforcing referential integrity.

Q13

Single Choice

Which MySQL statement is used to add a foreign key to an existing table?

ALTER TABLE ADD TABLE CREATE TABLEMODIFY TABLE

The ALTER TABLE statement in MySQL is used to add a foreign key constraint to an existing table.

Q14

Single Choice

In MySQL, what happens if you try to delete a record that is referenced by a foreign key in another table without specifying ON DELETE behavior?

The delete operation will succeed The delete operation will fail The record will be marked for deletionThe record will be deleted from both tables

Without specifying ON DELETE behavior, MySQL will prevent the deletion to maintain referential integrity, causing the delete operation to fail.

Q15

Single Choice

Which MySQL option can be used to automatically delete or update rows in a child table when the corresponding row in the parent table is deleted or updated?

CASCADE RESTRICT NO ACTIONSET DEFAULT

The CASCADE option in MySQL is used to automatically delete or update rows in a child table when the corresponding row in the parent table is deleted or updated.

Q16

Single Choice

How do you define a foreign key constraint with an ON DELETE CASCADE action in MySQL?

FOREIGN KEY (column_name) REFERENCES table_name (column_name) ON DELETE CASCADE FOREIGN KEY (column_name) REFERENCES table_name (column_name) CASCADE FOREIGN KEY (column_name) REFERENCES table_name (column_name) DELETE CASCADEFOREIGN KEY (column_name) REFERENCES table_name (column_name) CASCADE DELETE

The correct syntax to define a foreign key constraint with an ON DELETE CASCADE action in MySQL is FOREIGN KEY (column_name) REFERENCES table_name (column_name) ON DELETE CASCADE.

Q17

Single Choice

Which MySQL storage engine supports foreign key constraints?

MyISAM InnoDB MEMORYARCHIVE

In MySQL, only the InnoDB storage engine supports foreign key constraints.

Q18

Single Choice

What must be true about the columns involved in a foreign key relationship in MySQL?

The columns must have the same name The columns must have the same data type and size The columns must be primary keysThe columns must have unique constraints

The columns involved in a foreign key relationship in MySQL must have the same data type and size to ensure compatibility and referential integrity.

Q19

Single Choice

Which MySQL error is returned if you try to insert a value into a child table that does not exist in the parent table?

ERROR 1062 (23000): Duplicate entry ERROR 1217 (23000): Cannot delete or update a parent row ERROR 1452 (23000): Cannot add or update a child rowERROR 1054 (42S22): Unknown column

MySQL returns ERROR 1452 (23000) when you try to insert a value into a child table that does not exist in the parent table, indicating a foreign key constraint violation.

Q20

Single Choice

When creating a foreign key constraint in MySQL, which clause ensures that the changes made to the parent table are automatically reflected in the child table?

ON UPDATE CASCADE ON UPDATE RESTRICT ON UPDATE SET NULLON UPDATE NO ACTION

The ON UPDATE CASCADE clause ensures that changes made to the parent table are automatically propagated to the child table, maintaining referential integrity in MySQL.

Q21

Multiple Choice

Which SQL statement correctly creates a foreign key relationship between the 'orders' table and the 'customers' table?

SQL Code

CREATE TABLE orders (
 order_id INT AUTO_INCREMENT,
 customer_id INT,
 order_date DATE,
 PRIMARY KEY (order_id),
 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE orders ( order_id INT, customer_id INT, PRIMARY KEY (order_id) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT, customer_id INT, PRIMARY KEY (order_id) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT, customer_id INT, order_date DATE, PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );CREATE TABLE orders ( order_id INT, customer_id INT, FOREIGN KEY (order_id) REFERENCES customers(customer_id) );

A foreign key is used to establish a link between two tables by referencing the primary key of another table. The correct SQL statement defines this relationship clearly.

Q22

Multiple Choice

Identify the correct SQL code that defines a foreign key with cascading delete behavior.

SQL Code

CREATE TABLE orders (
 order_id INT AUTO_INCREMENT,
 customer_id INT,
 PRIMARY KEY (order_id),
 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
 ON DELETE CASCADE
);

CREATE TABLE orders ( order_id INT, customer_id INT, PRIMARY KEY (order_id) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT, customer_id INT, PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT, customer_id INT, PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE );CREATE TABLE orders ( order_id INT, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL );

In MySQL, the 'ON DELETE CASCADE' clause ensures that when a record in the parent table (customers) is deleted, the corresponding records in the child table (orders) are automatically deleted.

Q23

Multiple Choice

Which SQL code demonstrates the correct use of foreign keys to enforce referential integrity between 'employees' and 'departments' tables?

SQL Code

CREATE TABLE employees (
 employee_id INT AUTO_INCREMENT,
 department_id INT,
 employee_name VARCHAR(100),
 PRIMARY KEY (employee_id),
 FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE employees ( employee_id INT, department_id INT, PRIMARY KEY (employee_id) ); CREATE TABLE employees ( employee_id INT AUTO_INCREMENT, department_id INT, employee_name VARCHAR(100), PRIMARY KEY (employee_id), FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id INT, department_id INT, employee_name VARCHAR(100), PRIMARY KEY (employee_id) );CREATE TABLE departments ( department_id INT AUTO_INCREMENT, department_name VARCHAR(100), PRIMARY KEY (department_id) );

Referential integrity ensures that a foreign key value in the child table must match a primary key value in the parent table, preventing orphaned records.

Q24

Multiple Choice

Choose the SQL statement that correctly alters an existing table to add a foreign key constraint.

SQL Code

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id); ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id); ALTER TABLE orders ADD CONSTRAINT FOREIGN KEY (customer_id) REFERENCES customers(customer_id);ALTER TABLE orders ADD CONSTRAINT fk_customer PRIMARY KEY (customer_id) REFERENCES customers(customer_id);

The ALTER TABLE statement in MySQL is used to add a foreign key constraint to an existing table, ensuring that the referenced column in the child table matches a primary key in the parent table.

Q25

Multiple Choice

What SQL code would you use to define a foreign key that prevents deletion in the parent table if dependent records exist?

SQL Code

CREATE TABLE orders (
 order_id INT AUTO_INCREMENT,
 customer_id INT,
 PRIMARY KEY (order_id),
 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
 ON DELETE RESTRICT
);

CREATE TABLE orders ( order_id INT, customer_id INT, PRIMARY KEY (order_id) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT, customer_id INT, PRIMARY KEY (order_id) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT, customer_id INT, PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT );CREATE TABLE orders ( order_id INT, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL );

The 'ON DELETE RESTRICT' clause ensures that if there are any dependent records in the child table, the corresponding record in the parent table cannot be deleted.

Q26

Multiple Choice

Which SQL code correctly sets up a foreign key relationship in a junction table for a many-to-many relationship?

SQL Code

CREATE TABLE student_courses (
 student_id INT,
 course_id INT,
 PRIMARY KEY (student_id, course_id),
 FOREIGN KEY (student_id) REFERENCES students(student_id),
 FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE student_courses ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(student_id) ); CREATE TABLE student_courses ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) ); CREATE TABLE student_courses ( student_id INT, course_id INT, PRIMARY KEY (student_id) );CREATE TABLE student_courses ( student_id INT, course_id INT, PRIMARY KEY (course_id) );

In a many-to-many relationship, a junction table is used to link the two tables. The primary key of the junction table is typically a composite key, made up of the foreign keys from both related tables.

Q27

Multiple Choice

Select the SQL code that defines a foreign key with a SET NULL action on delete in the 'payments' table.

SQL Code

CREATE TABLE payments (
 payment_id INT AUTO_INCREMENT,
 order_id INT,
 amount DECIMAL(10, 2),
 PRIMARY KEY (payment_id),
 FOREIGN KEY (order_id) REFERENCES orders(order_id)
 ON DELETE SET NULL
);

CREATE TABLE payments ( payment_id INT, order_id INT, PRIMARY KEY (payment_id) ); CREATE TABLE payments ( payment_id INT AUTO_INCREMENT, order_id INT, PRIMARY KEY (payment_id) ); CREATE TABLE payments ( payment_id INT AUTO_INCREMENT, order_id INT, PRIMARY KEY (payment_id), FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE SET NULL );CREATE TABLE payments ( payment_id INT, order_id INT, FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE );

The 'ON DELETE SET NULL' clause ensures that when a record in the parent table is deleted, the corresponding foreign key in the child table is set to NULL, maintaining referential integrity without deleting the record in the child table.

Q28

Multiple Choice

Which SQL code is used to create a foreign key with a custom constraint name in the 'enrollments' table?

SQL Code

CREATE TABLE enrollments (
 enrollment_id INT AUTO_INCREMENT,
 student_id INT,
 course_id INT,
 PRIMARY KEY (enrollment_id),
 CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(student_id)
);

CREATE TABLE enrollments ( enrollment_id INT, student_id INT, PRIMARY KEY (enrollment_id) ); CREATE TABLE enrollments ( enrollment_id INT AUTO_INCREMENT, student_id INT, course_id INT, PRIMARY KEY (enrollment_id), FOREIGN KEY (student_id) REFERENCES students(student_id) ); CREATE TABLE enrollments ( enrollment_id INT AUTO_INCREMENT, student_id INT, course_id INT, PRIMARY KEY (enrollment_id), CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(student_id) );CREATE TABLE enrollments ( enrollment_id INT AUTO_INCREMENT, course_id INT, PRIMARY KEY (enrollment_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) );

In MySQL, you can define a custom name for a foreign key constraint using the CONSTRAINT keyword followed by the custom name. This helps in better identifying and managing constraints in large databases.

Q29

Multiple Choice

Identify the SQL code that prevents orphaned records by using foreign key constraints in the 'borrowings' table.

SQL Code

CREATE TABLE borrowings (
 borrowing_id INT AUTO_INCREMENT,
 book_id INT,
 member_id INT,
 PRIMARY KEY (borrowing_id),
 FOREIGN KEY (book_id) REFERENCES books(book_id)
 ON DELETE CASCADE,
 FOREIGN KEY (member_id) REFERENCES members(member_id)
 ON DELETE CASCADE
);

CREATE TABLE borrowings ( borrowing_id INT AUTO_INCREMENT, book_id INT, member_id INT, PRIMARY KEY (borrowing_id), FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE ); CREATE TABLE borrowings ( borrowing_id INT AUTO_INCREMENT, book_id INT, member_id INT, PRIMARY KEY (borrowing_id), FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE ); CREATE TABLE borrowings ( borrowing_id INT AUTO_INCREMENT, book_id INT, member_id INT, PRIMARY KEY (borrowing_id), FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE, FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE );CREATE TABLE borrowings ( borrowing_id INT, book_id INT, member_id INT, PRIMARY KEY (borrowing_id) );

Orphaned records occur when a record in a child table references a non-existent record in the parent table. The 'ON DELETE CASCADE' clause ensures that related records are deleted together, preventing orphaned records.

Q30

Multiple Choice

Which SQL code correctly defines a foreign key relationship and enforces referential integrity using a primary key and foreign key in the 'reviews' table?

SQL Code

CREATE TABLE reviews (
 review_id INT AUTO_INCREMENT,
 product_id INT,
 customer_id INT,
 review_text TEXT,
 PRIMARY KEY (review_id),
 FOREIGN KEY (product_id) REFERENCES products(product_id),
 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE reviews ( review_id INT AUTO_INCREMENT, product_id INT, PRIMARY KEY (review_id) ); CREATE TABLE reviews ( review_id INT AUTO_INCREMENT, product_id INT, customer_id INT, PRIMARY KEY (review_id) ); CREATE TABLE reviews ( review_id INT AUTO_INCREMENT, product_id INT, customer_id INT, review_text TEXT, PRIMARY KEY (review_id), FOREIGN KEY (product_id) REFERENCES products(product_id) );CREATE TABLE reviews ( review_id INT AUTO_INCREMENT, product_id INT, customer_id INT, review_text TEXT, PRIMARY KEY (review_id), FOREIGN KEY (product_id) REFERENCES products(product_id), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

Foreign keys are essential for enforcing referential integrity, ensuring that data in one table matches data in another. The SQL code provided correctly sets up these relationships in the 'reviews' table.

MySQL Database Quiz Questions