What action does SET NULL perform on foreign keys when the referenced record is deleted?…
- User
What action does SET NULL perform on foreign keys when the referenced record is deleted? Group of answer choices
Deletes the record
Sets foreign key to NULL
Updates the key
Leaves the key unchanged
Assistant
The correct answer is: Sets foreign key to NULL
Explanation:
- SET NULL is a referential action used in foreign key constraints.
- When the referenced record (primary key) is deleted, SET NULL automatically changes the foreign key value to
NULLin the dependent table. - This requires that the foreign key column allows NULL values (i.e., is not defined as
NOT NULL).
Why the other options are incorrect:
- Deletes the record → This describes
ON DELETE CASCADE. - Updates the key → This describes
ON UPDATE CASCADE(if the primary key changes). - Leaves the key unchanged → This would violate referential integrity unless the foreign key is manually handled.
Example:
CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL -- Sets customer_id to NULL if the referenced customer is deleted );Summary:
SET NULLensures that when a referenced row is deleted, the foreign key in dependent rows is set toNULLinstead of blocking the deletion (likeRESTRICT) or cascading the deletion (likeCASCADE).- User
Which of the following returns the total number of non-NULL values in a column? Group of answer choices
COUNT(*)
COUNT(column)
SUM(column)
AVG(column)
Assistant
The correct answer is: COUNT(column)
Explanation:
COUNT(column)counts only non-NULL values in the specified column.- If a column contains
NULLvalues, they are excluded from the count.
Why the other options are incorrect:
COUNT(*)→ Counts all rows, including those withNULLvalues in any column.SUM(column)→ Adds up numeric values in a column (ignoresNULLbut does not count them).AVG(column)→ Calculates the average of numeric values (ignoresNULLbut does not count them).
Example:
SELECT COUNT(salary) FROM employees; -- Counts only employees with a non-NULL salaryKey Difference:
- Use
COUNT(*)to count all rows (regardless ofNULL). - Use
COUNT(column)to count only non-NULL values in that column.
Summary:
If you need the total number of non-NULL values in a specific column,COUNT(column)is the correct choice.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.