Test Your Oracle Foreign Key Knowledge with This Challenging Quiz

Progress

True / False

In Oracle, a foreign key is a column or a set of columns that establishes a link between data in two tables.

True False

A foreign key is used to create a relationship between two tables by linking columns in one table to columns in another table.

True / False

In Oracle, a foreign key column can contain NULL values.

True False

Foreign key columns can contain NULL values, indicating that the relationship is optional.

True / False

In Oracle, a foreign key constraint automatically creates an index on the foreign key column(s).

True False

Oracle does not automatically create an index on foreign key columns, though it is often beneficial to create one for performance reasons.

True / False

In Oracle, a foreign key must reference a primary key in another table.

True False

A foreign key typically references a primary key or a unique key in another table to ensure referential integrity.

True / False

In Oracle, you can define a foreign key constraint at both the column level and the table level.

True False

Foreign key constraints can be specified directly in the column definition or separately after all column definitions at the table level.

True / False

In Oracle, a table can have multiple foreign keys.

True False

A table can have multiple foreign keys, each establishing a relationship with different parent tables.

True / False

In Oracle, cascading delete is an option where deleting a row in the parent table automatically deletes the related rows in the child table.

True False

The ON DELETE CASCADE option ensures that when a row in the parent table is deleted, the related rows in the child table are also deleted automatically.

True / False

In Oracle, you can disable a foreign key constraint temporarily without dropping it.

True False

Foreign key constraints can be disabled temporarily using the ALTER TABLE statement with the DISABLE keyword.

True / False

In Oracle, adding a foreign key constraint to a column with existing data that violates the constraint will result in an error.

True False

Oracle will return an error if you try to add a foreign key constraint to a column that contains data violating the constraint, ensuring referential integrity.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on creating, modifying, and managing foreign key constraints, as well as understanding their impact on database integrity and performance.

True False

The OCP certification covers advanced topics, including the creation, modification, and management of foreign key constraints, and understanding their impact on data integrity and query performance.

Q11

Single Choice

What is the primary purpose of a foreign key in an Oracle database?

To uniquely identify each row in a table To establish a relationship between two tables To enforce uniqueness in a columnTo store large text data

A foreign key is used to create a link between two tables, ensuring that the values in the foreign key column match values in the referenced primary key column of another table.

Q12

Single Choice

In Oracle SQL, which constraint must be enforced on a foreign key column?

NOT NULL UNIQUE REFERENCESCHECK

The REFERENCES constraint is used to define a foreign key, linking a column to the primary key in another table.

Q13

Single Choice

Which of the following SQL commands creates a foreign key on the department_id column of the employees table that references the departments table?

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id); CREATE FOREIGN KEY ON employees(department_id) REFERENCES departments(department_id); ALTER TABLE employees ADD PRIMARY KEY (department_id);CREATE FOREIGN KEY fk_dept ON employees(department_id) REFERENCES departments(department_id);

This command correctly adds a foreign key constraint to the employees table, linking the department_id column to the department_id column in the departments table.

Q14

Single Choice

Consider the following Oracle SQL query:What does this query do?

SQL Code

CREATE TABLE orders (
 order_id NUMBER PRIMARY KEY,
 customer_id NUMBER,
 CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Creates a table with a primary key on order_id and a foreign key on customer_id that references the customer_id column in the customers table Creates a table with a foreign key on order_id and a primary key on customer_id Creates a table with only a primary key on order_idCreates a table with no constraints

The query creates a table named orders with a primary key on order_id and a foreign key on customer_id, ensuring that each customer_id in orders matches a customer_id in the customers table.

Q15

Single Choice

Which Oracle SQL query will correctly delete the fk_customer foreign key constraint from the orders table?

ALTER TABLE orders DELETE FOREIGN KEY fk_customer; ALTER TABLE orders DROP FOREIGN KEY fk_customer; ALTER TABLE orders DROP CONSTRAINT fk_customer;DROP FOREIGN KEY fk_customer ON orders;

To remove a foreign key constraint in Oracle, the correct command is ALTER TABLE followed by DROP CONSTRAINT.

Q16

Single Choice

Given the Oracle SQL query:What does the ON DELETE CASCADE clause do?

SQL Code

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE;

Deletes the employees table if a department is deleted Deletes the related rows in the employees table when a row in the departments table is deleted Prevents deletion of rows in the departments tableDeletes the departments table if a related row is deleted in the employees table

The ON DELETE CASCADE clause ensures that when a row in the departments table is deleted, any related rows in the employees table are also deleted.

Q17

Single Choice

What will happen if you try to insert a value in the customer_id column of the orders table that does not exist in the customer_id column of the customers table (given that customer_id is a foreign key)?

The row will be inserted successfully The row will be updated An error will occurThe value will be automatically added to the customers table

Oracle enforces referential integrity, so inserting a value in a foreign key column that doesn't exist in the referenced table will result in an error.

Q18

Single Choice

Consider this Oracle SQL query:What does the ON DELETE SET NULL clause do?

SQL Code

CREATE TABLE employees (
 employee_id NUMBER PRIMARY KEY,
 department_id NUMBER,
 CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE SET NULL
);

Deletes the employees table if a department is deleted Sets the department_id to NULL in the employees table when a row in the departments table is deleted Prevents deletion of rows in the departments tableDeletes the departments table if a related row is deleted in the employees table

The ON DELETE SET NULL clause ensures that when a row in the departments table is deleted, the department_id in the employees table is set to NULL.

Q19

Single Choice

Which of the following is true about foreign keys and referential integrity in Oracle?

A foreign key must always reference a primary key in the same table A foreign key can reference a unique key in another table A table can have multiple foreign keys referencing different tablesA foreign key must be unique

In Oracle, a table can have multiple foreign keys, each referencing primary or unique keys in different tables.

Q20

Single Choice

Consider the following Oracle SQL scenario:What does this SQL code demonstrate?

SQL Code

CREATE TABLE orders (
 order_id NUMBER PRIMARY KEY,
 product_id NUMBER,
 customer_id NUMBER,
 CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
 CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL
);

The creation of multiple primary keys The creation of a composite foreign key The creation of two foreign keys with different referential actions on deleteThe use of product_id and customer_id as primary keys

The code creates two foreign keys: fk_product with ON DELETE CASCADE and fk_customer with ON DELETE SET NULL, demonstrating different referential actions on delete.

Q21

Multiple Choice

Identify the correct SQL statements that will successfully create a foreign key relationship in Oracle.

SQL Code

CREATE TABLE departments (
 department_id NUMBER PRIMARY KEY,
 department_name VARCHAR2(100)
);
CREATE TABLE employees (
 employee_id NUMBER PRIMARY KEY,
 department_id NUMBER,
 FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) );ALTER TABLE departments ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES employees(employee_id);

In Oracle, a foreign key constraint ensures referential integrity between two tables. The correct statements will define a foreign key relationship that references the primary key of another table.

Q22

Multiple Choice

Select the SQL statements that demonstrate how to handle foreign key constraints when deleting rows.

DELETE FROM departments WHERE department_id = 10; ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE; ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE SET NULL;ALTER TABLE employees DROP CONSTRAINT fk_dept;

Foreign key constraints can be configured with actions like CASCADE or SET NULL to control what happens to the related rows when a parent row is deleted. The correct statements will implement these actions.

Q23

Multiple Choice

Determine the SQL statements that demonstrate the use of foreign key constraints in a many-to-one relationship.

CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, CONSTRAINT fk_order_customer FOREIGN KEY (order_id) REFERENCES customers(customer_id) ); ALTER TABLE orders ADD CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);ALTER TABLE customers ADD CONSTRAINT fk_customer_order FOREIGN KEY (customer_id) REFERENCES orders(order_id);

A many-to-one relationship is established by having multiple rows in the child table (orders) reference a single row in the parent table (customer) via a foreign key constraint.

Q24

Multiple Choice

Identify the SQL statements that enforce referential integrity using foreign key constraints with cascading updates.

ALTER TABLE tasks ADD CONSTRAINT fk_task_project FOREIGN KEY (project_id) REFERENCES projects(project_id) ON UPDATE CASCADE; CREATE TABLE tasks ( task_id NUMBER PRIMARY KEY, project_id NUMBER, CONSTRAINT fk_task_project FOREIGN KEY (project_id) REFERENCES projects(project_id) ON UPDATE CASCADE ); ALTER TABLE projects ADD CONSTRAINT fk_project_task FOREIGN KEY (project_id) REFERENCES tasks(task_id) ON UPDATE CASCADE;CREATE TABLE tasks ( task_id NUMBER PRIMARY KEY, project_id NUMBER, CONSTRAINT fk_task_project FOREIGN KEY (task_id) REFERENCES projects(project_id) ON UPDATE CASCADE );

Cascading updates ensure that changes in the parent table (projects) are automatically reflected in the child table (tasks). The correct SQL statements will implement the ON UPDATE CASCADE action in the foreign key constraint.

Q25

Multiple Choice

Choose the SQL statements that correctly demonstrate the use of foreign key constraints in a self-referencing table.

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, manager_id NUMBER, CONSTRAINT fk_employee_manager FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ); ALTER TABLE employees ADD CONSTRAINT fk_employee_manager FOREIGN KEY (manager_id) REFERENCES employees(employee_id); CREATE TABLE employees ( employee_id NUMBER, manager_id NUMBER, CONSTRAINT fk_employee_manager FOREIGN KEY (employee_id) REFERENCES employees(manager_id) );ALTER TABLE employees DROP CONSTRAINT fk_employee_manager;

Self-referencing foreign keys are used to create hierarchical relationships within the same table, such as an employee reporting to a manager who is also an employee. The correct SQL statements will define the foreign key constraint accordingly.

Q26

Multiple Choice

Which SQL statements demonstrate how to modify an existing foreign key constraint to add cascading delete behavior?

ALTER TABLE tasks DROP CONSTRAINT fk_task_project; ALTER TABLE tasks ADD CONSTRAINT fk_task_project FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE; ALTER TABLE tasks MODIFY CONSTRAINT fk_task_project ON DELETE CASCADE; ALTER TABLE tasks DROP FOREIGN KEY fk_task_project; ALTER TABLE tasks ADD CONSTRAINT fk_task_project FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE;ALTER TABLE projects DROP CONSTRAINT fk_task_project; ALTER TABLE projects ADD CONSTRAINT fk_project_task FOREIGN KEY (project_id) REFERENCES tasks(task_id) ON DELETE CASCADE;

Modifying an existing foreign key constraint to add cascading delete behavior usually involves dropping the constraint and re-adding it with the ON DELETE CASCADE clause.

Q27

Multiple Choice

Determine the SQL statements that ensure referential integrity when inserting data into a child table with a foreign key constraint.

INSERT INTO orders (order_id, order_date) VALUES (1, '2024-01-01'); INSERT INTO order_items (item_id, order_id) VALUES (101, 1); INSERT INTO order_items (item_id, order_id) VALUES (101, 1); INSERT INTO orders (order_id, order_date) VALUES (1, '2024-01-01');INSERT INTO order_items (item_id, order_id) VALUES (101, NULL);

Referential integrity requires that each foreign key value in the child table ('order_items') must have a corresponding primary key value in the parent table ('orders'). The correct SQL statements will demonstrate how to enforce this constraint.

Q28

Multiple Choice

Identify the SQL statements that demonstrate the use of composite foreign keys in Oracle.

CREATE TABLE enrollments ( enrollment_id NUMBER PRIMARY KEY, student_id NUMBER, course_id NUMBER, CONSTRAINT fk_enrollment_course FOREIGN KEY (student_id, course_id) REFERENCES courses(student_id, course_id) ); CREATE TABLE enrollments ( enrollment_id NUMBER PRIMARY KEY, student_id NUMBER, course_id NUMBER, CONSTRAINT fk_enrollment_course FOREIGN KEY (student_id) REFERENCES courses(student_id) ); ALTER TABLE enrollments ADD CONSTRAINT fk_enrollment_course FOREIGN KEY (student_id, course_id) REFERENCES courses(student_id, course_id);CREATE TABLE enrollments ( enrollment_id NUMBER PRIMARY KEY, student_id NUMBER, course_id NUMBER, FOREIGN KEY (student_id, course_id) REFERENCES courses(student_id, course_id) );

Composite foreign keys reference a composite primary key in another table. The correct SQL statements will list all columns involved in the foreign key constraint and reference the corresponding composite primary key.

Q29

Multiple Choice

Select the SQL statements that correctly enforce foreign key constraints in a one-to-one relationship.

CREATE TABLE users ( user_id NUMBER PRIMARY KEY, username VARCHAR2(50) ); CREATE TABLE user_profiles ( profile_id NUMBER PRIMARY KEY, user_id NUMBER UNIQUE, CONSTRAINT fk_user_profile FOREIGN KEY (user_id) REFERENCES users(user_id) ); CREATE TABLE users ( user_id NUMBER PRIMARY KEY, username VARCHAR2(50) ); CREATE TABLE user_profiles ( profile_id NUMBER PRIMARY KEY, user_id NUMBER, CONSTRAINT fk_user_profile FOREIGN KEY (user_id) REFERENCES users(user_id) ); ALTER TABLE user_profiles ADD CONSTRAINT fk_user_profile FOREIGN KEY (user_id) REFERENCES users(user_id);CREATE TABLE user_profiles ( profile_id NUMBER PRIMARY KEY, user_id NUMBER, CONSTRAINT fk_user_profile FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE );

A one-to-one relationship is enforced by using a foreign key that is also unique or a primary key in the child table. The correct SQL statements will ensure this relationship is properly constrained.

Q30

Multiple Choice

Choose the SQL statements that demonstrate how to troubleshoot foreign key constraint violations in Oracle.

SELECT * FROM orders WHERE order_id = 1; INSERT INTO order_items (item_id, order_id) VALUES (101, 1); INSERT INTO order_items (item_id, order_id) VALUES (101, 2); COMMIT; DELETE FROM orders WHERE order_id = 1; INSERT INTO order_items (item_id, order_id) VALUES (101, 1);ALTER TABLE order_items DISABLE CONSTRAINT fk_order_item;

Foreign key constraint violations occur when a value in the child table does not match any value in the parent table. The correct SQL statements will include queries to identify the issue and methods to resolve it.

Oracle Database Quiz Questions