Test Your PostgreSQL Foreign Key Knowledge with this Quiz

Progress

True / False

In PostgreSQL, a foreign key is used to link two tables together.

True False

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table, creating a link between the two tables.

True / False

A foreign key in PostgreSQL can reference columns in the same table.

True False

This is known as a self-referential foreign key, where a table's foreign key references its own primary key.

True / False

In PostgreSQL, foreign keys do not enforce referential integrity.

True False

Foreign keys enforce referential integrity by ensuring that the value in the foreign key column(s) matches a value in the referenced table's primary key column(s).

True / False

In PostgreSQL, you can define a foreign key constraint using the ALTER TABLE statement.

True False

You can add a foreign key constraint to an existing table using the ALTER TABLE statement.

True / False

A foreign key in PostgreSQL can reference a unique constraint instead of a primary key.

True False

PostgreSQL allows foreign keys to reference columns with unique constraints as well as primary keys.

True / False

PostgreSQL automatically creates an index on the foreign key column.

True False

PostgreSQL does not automatically create an index on the foreign key column, though creating an index can improve performance.

True / False

In PostgreSQL, ON DELETE CASCADE ensures that when a referenced row is deleted, all rows with matching foreign key values are also deleted.

True False

ON DELETE CASCADE is an action that can be defined for a foreign key to automatically delete rows in the referencing table when the referenced row is deleted.

True / False

PostgreSQL allows foreign key constraints to be deferred.

True False

PostgreSQL supports deferrable foreign key constraints, which can be set to check constraints at the end of the transaction rather than immediately.

True / False

In PostgreSQL, you cannot define a foreign key constraint that references multiple columns (composite key).

True False

PostgreSQL supports composite foreign keys that reference multiple columns.

Q10

True / False

In PostgreSQL, foreign key constraints can reference tables in other databases.

True False

Foreign key constraints in PostgreSQL can only reference tables within the same database. They cannot reference tables in different databases.

Q11

Single Choice

In PostgreSQL, what is the primary purpose of a foreign key?

o enforce data types o maintain referential integrity between two tables To index the columns of a tableTo set default values in columns

A foreign key in PostgreSQL is used to enforce a link between two tables, ensuring that the values in one table correspond to valid entries in another.

Q12

Single Choice

Which keyword is used in a PostgreSQL table creation statement to define a foreign key constraint?

UNIQUE CHECK FOREIGN KEYPRIMARY KEY

The FOREIGN KEY keyword is used to define a foreign key constraint in PostgreSQL, linking columns in one table to primary key columns in another.

Q13

Single Choice

In PostgreSQL, which of the following statements correctly creates a foreign key?

SQL Code

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

The statement is correct The foreign key should reference a different table The foreign key should be declared without a constraint nameThe primary key should be declared after the foreign key

The SQL statement correctly creates a foreign key constraint in PostgreSQL that links the customer_id column in the orders table to the customer_id column in the customers table.

Q14

Single Choice

What happens in PostgreSQL when a record in a parent table with a foreign key reference is deleted?

The corresponding records in the child table are automatically deleted. The corresponding records in the child table are set to NULL. An error occurs unless an ON DELETE action is specified.The foreign key reference is automatically updated.

In PostgreSQL, if you try to delete a record in a parent table without specifying an ON DELETE action, an error occurs due to the foreign key constraint.

Q15

Single Choice

Which of the following SQL statements adds a foreign key constraint to an existing table in PostgreSQL?

SQL Code

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

This statement is incorrect The constraint should be added during table creation only The statement is correctThe foreign key should reference the same table

This SQL statement correctly adds a foreign key constraint to the orders table after it has already been created.

Q16

Single Choice

In PostgreSQL, which of the following statements creates a table with a composite foreign key?

SQL Code

CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 CONSTRAINT fk_order_product FOREIGN KEY (order_id, product_id) REFERENCES orders(order_id, product_id)
);

The statement is correct The foreign key must reference a single column The foreign key constraint should be defined in the orders tableComposite foreign keys are not allowed in PostgreSQL

PostgreSQL allows composite foreign keys, where the foreign key constraint references multiple columns in another table

Q17

Single Choice

Which of the following ON DELETE actions ensures that when a referenced row in PostgreSQL is deleted, all associated rows in the referencing table are also deleted?

ON DELETE SET NULL ON DELETE CASCADE ON DELETE RESTRICTON DELETE NO ACTION

The ON DELETE CASCADE action ensures that when a referenced row is deleted, all rows in the child table that reference it are also deleted.

Q18

Single Choice

What does the following PostgreSQL SQL statement do?

SQL Code

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

Deletes all orders when a payment is deleted Sets the order_id to NULL in payments when an order is deleted Deletes payments when an order is deletedPrevents deletion of orders with corresponding payments

The ON DELETE SET NULL action sets the order_id column in the payments table to NULL if the corresponding order_id in the orders table is deleted.

Q19

Single Choice

How would you modify an existing foreign key constraint in PostgreSQL to include the ON UPDATE CASCADE option?

SQL Code

ALTER TABLE orders DROP CONSTRAINT fk_customer, ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON UPDATE CASCADE;

The statement is incorrect; foreign keys cannot be modified The statement is correct The ON UPDATE clause must be added during table creationThe ALTER command should not drop the existing constraint

This SQL statement correctly drops the existing foreign key constraint and re-adds it with the ON UPDATE CASCADE option

Q20

Single Choice

Consider the following tables in PostgreSQL:

SQL Code

CREATE TABLE customers (
 customer_id SERIAL PRIMARY KEY,
 customer_name VARCHAR(100)
);

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 customer_id INT,
 CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT
);
What happens if you attempt to delete a customer who has orders?

The customer is deleted, and the customer_id in orders is set to NULL. The deletion of the customer is restricted, and an error is raised. All associated orders are deleted with the customer.The customer_id in orders is updated automatically.

The ON DELETE RESTRICT action prevents the deletion of a customer if there are associated orders in the orders table, raising an error instead.

Q21

Multiple Choice

What is the primary role of a foreign key in PostgreSQL?

SQL Code

CREATE TABLE departments (
 department_id SERIAL PRIMARY KEY,
 department_name VARCHAR(100)
);

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 first_name VARCHAR(50),
 department_id INT REFERENCES departments(department_id)
);

To establish a relationship between two tables. To enforce uniqueness within a single table. To ensure that all values in a column are unique.To automatically generate values for a column.

A foreign key in PostgreSQL establishes a relationship between two tables by linking a column in one table to the primary key of another table.

Q22

Multiple Choice

Which of the following is true about foreign keys in PostgreSQL?

SQL Code

CREATE TABLE customers (
 customer_id SERIAL PRIMARY KEY,
 customer_name VARCHAR(100)
);

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

A foreign key ensures referential integrity between tables. A foreign key allows NULL values by default. A foreign key enforces uniqueness in a single table.A foreign key cannot reference a composite key.

A foreign key in PostgreSQL enforces referential integrity by ensuring that values in a column correspond to values in another table's primary key column.

Q23

Multiple Choice

How can you define a foreign key constraint in PostgreSQL?

SQL Code

CREATE TABLE courses (
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR(100)
);

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100),
 course_id INT REFERENCES courses(course_id)
);

Using the REFERENCES keyword. Using the SERIAL keyword. Using the UNIQUE keyword.Using the PRIMARY KEY keyword.

A foreign key in PostgreSQL can be defined using the REFERENCES keyword, which links a column in one table to the primary key of another table.

Q24

Multiple Choice

What happens if you try to insert a value in a foreign key column that does not exist in the referenced primary key column?

SQL Code

CREATE TABLE library_branches (
 branch_id SERIAL PRIMARY KEY,
 branch_name VARCHAR(100)
);

CREATE TABLE books (
 book_id SERIAL PRIMARY KEY,
 title VARCHAR(100),
 branch_id INT REFERENCES library_branches(branch_id)
);

INSERT INTO books (title, branch_id) VALUES ('PostgreSQL Essentials', 999);

A foreign key constraint violation error is raised. The value is automatically added to the referenced table. The insertion is ignored.The row is inserted with a NULL value in the foreign key column.

In PostgreSQL, attempting to insert a value in a foreign key column that does not exist in the referenced primary key column will result in a foreign key constraint violation error.

Q25

Multiple Choice

Can a foreign key reference a unique constraint instead of a primary key in PostgreSQL?

SQL Code

Create a table 'employees' with a unique constraint on 'email', and another table 'departments' with a foreign key 'manager_email' referencing 'employees' on the 'email' column.

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 email VARCHAR(100) UNIQUE,
 first_name VARCHAR(50)
);

CREATE TABLE departments (
 department_id SERIAL PRIMARY KEY,
 department_name VARCHAR(100),
 manager_email VARCHAR(100) REFERENCES employees(email)
);

Yes, it can reference a unique constraint. No, it can only reference a primary key. Yes, but only if the unique constraint is on a single column.No, it must always reference a primary key.

In PostgreSQL, a foreign key can reference a unique constraint in another table, not just the primary key. This allows flexibility in database design.

Q26

Multiple Choice

How do cascading updates and deletes work with foreign keys in PostgreSQL?Create a table 'categories' and 'products' where deleting a category cascades to delete all associated products.

SQL Code

CREATE TABLE categories (
 category_id SERIAL PRIMARY KEY,
 category_name VARCHAR(100)
);

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_name VARCHAR(100),
 category_id INT REFERENCES categories(category_id) ON DELETE CASCADE
);

INSERT INTO categories (category_name) VALUES ('Electronics');
INSERT INTO products (product_name, category_id) VALUES ('Smartphone', 1);
DELETE FROM categories WHERE category_id = 1;

Cascading deletes remove all related rows in the referencing table. Cascading updates automatically update related rows. Cascading actions can be defined using ON DELETE and ON UPDATE clauses.Cascading actions are mandatory for all foreign keys.

Cascading updates and deletes in PostgreSQL ensure that changes in the referenced table (e.g., deletion of a row) automatically propagate to the referencing table, maintaining referential integrity.

Q27

Multiple Choice

What is the purpose of the ON DELETE SET NULL option in a foreign key constraint?Create a table 'projects' with a foreign key 'leader_id' referencing 'employees'. Use the ON DELETE SET NULL option.

SQL Code

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100)
);

CREATE TABLE projects (
 project_id SERIAL PRIMARY KEY,
 project_name VARCHAR(100),
 leader_id INT REFERENCES employees(employee_id) ON DELETE SET NULL
);

INSERT INTO employees (employee_name) VALUES ('Alice');
INSERT INTO projects (project_name, leader_id) VALUES ('New Project', 1);
DELETE FROM employees WHERE employee_id = 1;

Sets the foreign key column to NULL if the referenced row is deleted. Automatically deletes the referencing row if the referenced row is deleted. Prevents the deletion of the referenced row.Updates the foreign key to a default value on deletion.

The ON DELETE SET NULL option in PostgreSQL allows foreign key columns to be set to NULL if the referenced row is deleted, avoiding orphaned records while maintaining referential integrity.

Q28

Multiple Choice

How can you create a composite foreign key in PostgreSQL?Create a table 'registrations' with a composite foreign key referencing 'students' and 'courses'.

SQL Code

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100)
);

CREATE TABLE courses (
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR(100)
);

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

By referencing multiple columns in the foreign key definition. By using the UNIQUE constraint on multiple columns. By creating separate foreign keys for each column.By using the CHECK constraint.

A composite foreign key in PostgreSQL consists of multiple columns that reference corresponding columns in another table, ensuring that combinations of values exist in the referenced table.

Q29

Multiple Choice

Can a foreign key reference a primary key in the same table in PostgreSQL?Create a self-referencing foreign key in a table 'employees' where 'manager_id' references 'employee_id' in the same table.

SQL Code

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100),
 manager_id INT REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_name, manager_id) VALUES ('John', NULL);
INSERT INTO employees (employee_name, manager_id) VALUES ('Doe', 1);

Yes, it can reference a primary key in the same table. No, foreign keys must reference different tables. Yes, but only if the foreign key column is unique.No, it requires a unique constraint instead.

In PostgreSQL, a foreign key can reference a primary key in the same table, commonly used for hierarchical data structures like employee-manager relationships.

Q30

Multiple Choice

What does the ON UPDATE CASCADE clause do in a foreign key definition in PostgreSQL?Create a table 'authors' and 'books' where updating an 'author_id' in 'authors' cascades the update to 'books'.

SQL Code

CREATE TABLE authors (
 author_id SERIAL PRIMARY KEY,
 author_name VARCHAR(100)
);

CREATE TABLE books (
 book_id SERIAL PRIMARY KEY,
 book_title VARCHAR(100),
 author_id INT REFERENCES authors(author_id) ON UPDATE CASCADE
);

INSERT INTO authors (author_name) VALUES ('George Orwell');
INSERT INTO books (book_title, author_id) VALUES ('1984', 1);
UPDATE authors SET author_id = 2 WHERE author_id = 1;

Automatically updates the foreign key when the referenced key is updated. Prevents updates to the referenced key. Deletes the referencing row if the referenced key is updated.Ignores updates to the referenced key.

The ON UPDATE CASCADE clause ensures that if the primary key in the referenced table is updated, the corresponding foreign key in the referencing table is automatically updated as well, maintaining data consistency.

PostgreSQL Database Quiz Questions