What are the differences between ON DELETE CASCADE and ON DELETE SET NULL? What is a super key, and how does it relate to candidate keys? Can a foreign key reference a non-primary key column in another table?

Question

Accepted Answer

**ON DELETE CASCADE** → When a parent row is deleted, all related child rows are automatically deleted. **ON DELETE SET NULL** → When a parent row is deleted, the foreign key column in the child rows is set to NULL. **Requirement**: SET NULL requires the foreign key column to allow NULL values. **Usage**: CASCADE is used when child data should not exist without its parent; SET NULL is used when child records should remain but lose the relationship. FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  ON DELETE CASCADE;  -- deletes child rows

FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  ON DELETE SET NULL; -- sets FK to NULL A **super key** is any column or combination of columns that can uniquely identify a row in a table. It may contain extra, unnecessary attributes. **All candidate keys are super keys**, but not all super keys are candidate keys. A super key becomes a **candidate key** only when it has no unnecessary columns. Columns: (emp_id), (email), (emp_id, name) (emp_id) → Candidate Key (minimal) (email) → Candidate Key (minimal) (emp_id, name) → Super Key but *not* a candidate key because it has extra attributes. Yes — a foreign key **can reference any column** in another table as long as that column is indexed with either: • a PRIMARY KEY constraint, or • a UNIQUE constraint. The referenced column must have **unique values** so MySQL can enforce referential integrity. CREATE TABLE departments (
    dept_code VARCHAR(10) UNIQUE,
    name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_code VARCHAR(10),
    FOREIGN KEY (dept_code) REFERENCES departments(dept_code)
); Foreign keys do *not* need to reference primary keys — they only need to reference a uniquely indexed column.

What are the differences between ON DELETE CASCADE and ON DELETE SET NULL? What is a super key, and how does it relate to candidate keys? Can a foreign key reference a non-primary key column in another table?

Advanced Key Concepts and Foreign Key Behaviors

What are the differences between ON DELETE CASCADE and ON DELETE SET NULL? What is a super key, and how does it relate to candidate keys? Can a foreign key reference a non-primary key column in another table?

Advanced Key Concepts and Foreign Key Behaviors

Scenario Questions

Follow-up Questions