The letter that means least
Consistency in ACID means every transaction moves the database from a state satisfying your rules to another state satisfying them. The rules are the ones you declared: keys are unique, references point at rows that exist, a balance may not go negative, one account per email address.
If your transaction would land somewhere that breaks a rule, the database refuses it and atomicity rolls the whole thing back. Read the two letters as a pair. Consistency defines what invalid means, and atomicity makes invalid states unreachable rather than merely discouraged.
This letter is the odd one out. Atomicity, isolation and durability are things the engine provides by itself. Consistency depends on you declaring the rules, because the database can only enforce what it knows about. A rule like “the entries for one transfer must sum to zero” cannot be a column constraint, so it holds only because your transactions preserve it and isolation stops others interleaving halfway.
Where validation belongs
The argument about where validation belongs, in application code or in database constraints. Your application checks give friendlier errors, and every check you write only in the application is a race waiting to happen.
Two requests both checking that an email is free at the same instant. Both pass, both insert. Only a constraint in the database closes that window, because the database is the one place both requests have to queue.
Use both layers, with the database as the last line. And keep this C separate from CAP's C, which is about replicas agreeing rather than rules holding. Interviewers enjoy that collision of names.
Worked example
Aisha's team ships signup validation in their Node app: check if the email exists, then insert. Load testing at 500 signups per second surfaces duplicates within the hour, because two requests for jake@example.com pass the check in the same 5 ms window and both insert. Downstream, password resets start going to the wrong row of the pair, which is how the bug is actually noticed. The fix is a UNIQUE constraint on users.email. Now one of any racing pair fails with a constraint violation error 23505, which the app catches and turns into "email already registered." The app-level check stays for fast, friendly feedback, but the constraint is what makes the invariant true. Duplicates found in the following quarter: zero.