Drill 10 of 10 in Database Analyst
0

Database Analyst · UNIQUE · CHECK · NULL semantics · UNION

10. Rows that would block a constraint

Concept: UNIQUE · CHECK · NULL semantics · UNIONshow

ALTER TABLE ... ADD CONSTRAINT fails if any existing row breaks it, so find those rows first. UNIQUE compares exact values and lets any number of NULLs through. A CHECK only fails when its condition is false, so a NULL makes the check pass.

Example (different data):

-- Before: ALTER TABLE devices ADD UNIQUE (serial);
SELECT id, serial
FROM (
  SELECT id, serial, COUNT(*) OVER (PARTITION BY serial) AS n
  FROM devices
) d
WHERE serial IS NOT NULL AND n > 1;

Your task

The DBA wants to add UNIQUE (account_code) and CHECK (credit_limit >= 0) to legacy_accounts, and both ALTER TABLE statements will fail until the offending rows are fixed.

Return id, account_code, credit_limit and problem for every legacy_accounts row that would block one of the two constraints: problem 'duplicate code' for each row whose account_code exactly equals another row's, and 'negative limit' for each row with credit_limit < 0 (a row breaking both would appear twice). Sort by id, then problem.

Starting Postgres in your browser…

Checking your access…