What does the WITH CHECK OPTION clause on a view definition do, and how does WITH LOCAL CHECK OPTION differ from WITH CASCADED CHECK OPTION?
answer
- default: predicate applies on read only
- rows can escape through writes
- CHECK OPTION = predicate enforced on write
- LOCAL = this view + views that opted in
- CASCADED = whole chain; standard default
basics
~20 sWITH CHECK OPTION makes the engine reject writes that would produce rows the view itself cannot see. LOCAL enforces only the predicates of the view where the clause is written; CASCADED also enforces the predicates of every underlying view in the chain.
solid answer
~60 sBy default a view's WHERE clause is applied only on read, so a write through the view can create or move a row outside it — inserting a row the view will never show, or updating a visible row so it vanishes. WITH CHECK OPTION closes that hole: the engine evaluates the resulting row against the view predicate and raises an error if it fails. The LOCAL/CASCADED distinction only matters for views built on other views. **LOCAL** enforces the predicate of the view carrying the clause, plus the predicates of underlying views that themselves declared a check option — it does not force checking on views that did not. **CASCADED** enforces the predicates of the whole underlying chain regardless of what those views declared. CASCADED is the SQL standard's default when you write plain WITH CHECK OPTION, and it is what you want for a security or tenancy view: it guarantees no write through the view can produce a row outside the visible slice. LOCAL is the loophole-prone choice, and is worth naming explicitly if you ever use it.
code
sql · 5 linesCREATE VIEW open_orders AS
SELECT order_id, status, total FROM orders WHERE status = 'OPEN';
INSERT INTO open_orders (order_id, status, total) VALUES (9, 'CLOSED', 50);
-- succeeds; the row is in orders but invisible through open_ordersgo deeper
Know that without the clause a write can create rows the view cannot see, and that the clause makes the engine reject them.
Explain LOCAL versus CASCADED precisely in terms of view chains and state that a bare WITH CHECK OPTION means CASCADED in the standard.
Position it as making a view's read filter symmetric on write, note it does not bind base-table writers, and flag that adding it to a live view is a behaviour change for existing callers.
Decide where the invariant belongs — table constraint or row-security policy for a system-wide rule, check option only to stop the view itself becoming a write-laundering path.
## The problem it solves A view predicate is a read-time filter. Consider a view over orders restricted to status = 'OPEN'. Nothing in the default semantics stops you from inserting a row with status = 'CLOSED' through that view: the insert is rewritten onto the base table, the row is stored, and the view simply cannot see it afterwards. The same applies to an update that changes a visible row's status — it succeeds and the row disappears from the view. Neither is an error. This is more than an oddity when the view is an access-control boundary. If a tenant-scoped view restricts rows to tenant_id = current_tenant() and a caller has INSERT on the view but nothing on the base table, the caller can, without WITH CHECK OPTION, insert rows attributed to *another* tenant. They cannot read them back through the view, but they have written into someone else's data. WITH CHECK OPTION turns the read filter into a write constraint as well. ## What the clause does Declaring a view WITH CHECK OPTION tells the engine: after computing the row that an INSERT or UPDATE through this view would produce, evaluate the view's predicate against that row; if it is false (or unknown), reject the statement with an error rather than write it. Deletes need no check — a deleted row cannot violate a predicate. Two details are worth knowing. The check runs against the *resulting* row, not the input, so defaults and generated columns are already applied. And NULL semantics bite: a predicate that evaluates to UNKNOWN counts as a failure, so inserting a row with NULL in a column mentioned by the predicate is refused. ## LOCAL versus CASCADED The distinction exists only for view chains — a view defined over another view. Suppose base view A restricts to region = 'EU', and view B selects from A restricting to amount > 100, and you write through B. - **WITH CASCADED CHECK OPTION on B**: the engine enforces B's predicate *and* A's predicate, and every predicate further down the chain, whether or not those views declared any check option. A write producing region = 'US' is rejected. - **WITH LOCAL CHECK OPTION on B**: the engine enforces B's own predicate, and the predicates of underlying views that themselves declared a check option. If A declared none, A's predicate is not enforced, so a write producing region = 'US', amount = 500 succeeds and simply disappears from both views. In the SQL standard, a bare WITH CHECK OPTION means CASCADED. Vendors are broadly consistent on this, but the safest habit is to write the keyword explicitly so no reader has to know the default. Why would LOCAL ever be wanted? It lets a view author add a restriction of their own without imposing enforcement on layers they do not own — for example a convenience view over a broad, deliberately permissive base view where writes outside the base predicate are legitimate. In practice that is rare, and LOCAL on a security-relevant chain is usually a defect. ## Where it fits among constraints WITH CHECK OPTION is not a substitute for CHECK constraints, foreign keys, or row-level security. It is scoped to writes that arrive *through that view* — a caller with direct base-table privileges bypasses it entirely. It is best understood as making the view's own contract symmetric between read and write, on top of whatever the base table enforces for everyone. If a rule must hold for all writers, put it on the table as a constraint or a row-security policy; use the check option to stop the view itself from being a laundering channel. It also interacts with INSTEAD OF triggers: when a trigger takes over the write, the engine's automatic rewrite is not what executes, and the check option is generally not applied to what the trigger does. If you replace auto-updatability with a trigger, you inherit the responsibility of validating the row against the view's intent yourself. ## Operational notes Adding the clause to an existing view is a behaviour change for writers: statements that used to succeed silently now fail. That is usually a bug fix, but it should be deployed knowingly, ideally after auditing whether any rows currently outside the predicate arrived through the view. Because the check is a per-row predicate evaluation on write, its cost is negligible next to the write itself; performance is not a reason to omit it. ## Interview framing Lead with the escaping-row problem and a concrete example, then define the clause, then explain LOCAL versus CASCADED strictly in terms of view chains, noting CASCADED is the standard's default and the right choice for security views. Mentioning that it does not constrain callers who write to the base table directly shows you understand its scope.
- Does WITH CHECK OPTION stop a user from inserting an out-of-scope row if they also hold INSERT on the base table?No. The clause only governs writes routed through that view; a direct INSERT on the base table never evaluates it. If the restriction must hold for every writer, express it as a CHECK constraint, a foreign key, or a row-level security policy on the table, and revoke direct write privileges so the view is the only path.
- Is the check option applied to DELETE statements?No, and it does not need to be. A delete removes a row, and a removed row cannot violate the view's predicate — anything visible through the view already satisfied it. The clause matters only for INSERT and UPDATE, where a new or modified row could fall outside the view.
saying these in an interview costs you the question
- Believing a view's WHERE clause automatically prevents writing rows outside it
- Saying LOCAL versus CASCADED matters for a view defined directly on a table
- Treating WITH CHECK OPTION as protection against users who write to the base table directly
- Assuming an INSTEAD OF trigger still gets the check option enforced for it
- Claiming the clause blocks deletes of rows outside the predicate