SQL Database Keys and Relationship Core Quiz

A customer table assigns each customer a unique ID so the system can distinguish records even when names repeat. Which type of key ensures this guaranteed uniqueness?

A system links orders to customers so every order belongs to exactly one customer. The column storing the customer’s ID inside the orders table represents which type of relationship mechanism?

A department can have many employees, but each employee works in exactly one department. How would you describe this relationship?

A library system tracks which books are borrowed by which users, and each book may be borrowed many times while users may borrow multiple books. Which structure handles this?

In a products table, the SKU value uniquely identifies each product and cannot be NULL. Which characteristic matches this kind of column?

A foreign key rule ensures that an order cannot reference a customer who does not exist. This protective behavior is known as which kind of integrity?

In a student–course system, each student may enroll in many courses, and each course may have many students. What table structure typically represents this relationship?

A manager table references employees, but only those employees who are designated as managers. A foreign key ensures the referenced employee exists. What does this prevent?

If a product category is deleted, a cascading rule may remove all products in that category. Which type of action handles this dependent removal?

A one-to-one relationship appears when an item has exactly one detail row in another table, such as a profile having exactly one settings row. How is this often enforced?

A contact table uses a composite key of (country_code, phone_number) because both together uniquely identify a contact. What key property does this illustrate?

A movie may have multiple actors, and actors may star in multiple movies. Which pattern describes this association?

A table requires each row to reference another table's primary key. What constraint enforces this?

A user table stores an optional referral ID pointing back to another user. This type of reference within the same table demonstrates which pattern?

A payments table links each payment to one order. However, an order may have multiple payments. How should this relationship be classified?

A design team chooses surrogate keys (auto-incremented IDs) instead of natural keys. Why might this be useful?

A warehouse table lists locations, while an items table lists products stored in those locations. The items table includes a location_id column referencing the warehouse table. What role does location_id play?

A star-schema style fact table references several dimension tables. Each reference prevents missing dimension rows from being linked. Which principle governs this structure?

A contract must belong to exactly one customer, but a customer may have multiple contracts. What relationship best describes this rule?

A relationship between two tables indicates that rows on one side cannot exist without matching rows on the other. Which mechanism enforces this dependency?

A blog post can have many comments, but each comment refers to only one post. Which structure represents this rule?

If a foreign key field is allowed to be NULL, what does this imply about the relationship?

A many-to-many mapping requires storing pairs of references. How is uniqueness typically enforced in such a link table?

A database prohibits deleting a customer who still has active orders. Which foreign key option enforces this behavior?

A user profile table references a user account table. If deleting a user sets the profile’s foreign key to NULL instead of deleting the profile, which behavior is this?

This table definition creates a unique identifier for each row. What does the highlighted column represent?

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

This table stores orders referencing customers. What does the customer_id column enforce?

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id)
);

This relationship links employees to departments. What kind of structure does it represent?

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  dept_id INT REFERENCES departments(id)
);

In this linking table, what purpose do the two REFERENCES lines serve?

CREATE TABLE enrollment (
  student_id INT REFERENCES students(id),
  course_id INT REFERENCES courses(id)
);

What does this structure enforce regarding user settings?

CREATE TABLE settings (
  user_id INT UNIQUE REFERENCES users(id),
  theme TEXT
);

This table prevents deleting categories that still have products. Which rule makes this happen?

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category_id INT REFERENCES categories(id) ON DELETE RESTRICT
);

This column refers to another row in the same table. What type of reference is created?

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  manager_id INT REFERENCES employees(id)
);

This entry pairs a student with a class. What does the pair represent?

INSERT INTO enrollment (student_id, class_id) VALUES (10, 4);

This query finds all orders belonging to customer 3. Which mechanism makes this relationship possible?

SELECT * FROM orders WHERE customer_id = 3;

What does this delete action cause?

DELETE FROM customers WHERE id = 10;

This structure creates a link row storing book–author relationships. What does it represent?

INSERT INTO book_authors (book_id, author_id) VALUES (5, 2);

This definition ensures that each comment relates to a blog post. Which relationship is created?

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT REFERENCES posts(id)
);

This foreign key links each order item to its order. What structure emerges?

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id)
);

This update changes the customer linked to an order. What does it rely on?

UPDATE orders SET customer_id = 7 WHERE id = 3;

This insert attempts to add a product belonging to category 99. What requirement must be met?

INSERT INTO products (name, category_id) VALUES ('Lamp', 99);

This join table stores the association of tags to posts. What role does each row play?

INSERT INTO post_tags (post_id, tag_id) VALUES (5, 3);

This design sets a foreign key to NULL when its parent row is deleted. What behavior is this?

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  project_id INT REFERENCES projects(id) ON DELETE SET NULL
);

This table holds product images. Each image references a product. What structure is formed?

CREATE TABLE product_images (
  id SERIAL PRIMARY KEY,
  product_id INT REFERENCES products(id)
);

This cascading delete rule ensures consistency. What effect does it produce?

CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id) ON DELETE CASCADE
);

This change updates the relationship between an employee and department. What constraint ensures the new department exists?

UPDATE employees SET dept_id = 4 WHERE id = 12;

This structure defines order–product links. What pattern does it create?

CREATE TABLE order_products (
  order_id INT REFERENCES orders(id),
  product_id INT REFERENCES products(id)
);

This insert attempts to assign an employee to a team. What requirement must be satisfied first?

INSERT INTO team_members (team_id, employee_id) VALUES (2, 15);

This definition pairs categories and tags. What type of relationship table is it?

CREATE TABLE category_tags (
  category_id INT REFERENCES categories(id),
  tag_id INT REFERENCES tags(id)
);

This operation attempts to remove a project. What determines whether it succeeds?

DELETE FROM projects WHERE id = 6;

This table links roles and permissions. Each row binds one role to one permission. What relationship is defined?

CREATE TABLE role_permissions (
  role_id INT REFERENCES roles(id),
  perm_id INT REFERENCES permissions(id)
);