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…