← 🗄️ SQL
Subtopic 5 of 5

3.5Views, constraints, triggers and DDL

The idea

Views, constraints and triggers add structure around tables. A view is a saved query; constraints keep data valid; a trigger fires automatically on a change.

Think of it like this: A view is a window onto the data; constraints are guard rails; a trigger is a tripwire alarm.

Why it exists

Statements about updatable views, UNIQUE with NULL and CHECK show up as true/false questions.

How it works

  1. View: no data stored; updatable only if it maps to a single base table with no aggregates, DISTINCT or GROUP BY.
  2. PRIMARY KEY = UNIQUE + NOT NULL. UNIQUE allows NULLs.
  3. CHECK passes if the condition is TRUE or UNKNOWN.
  4. DDL changes structure (CREATE, ALTER, DROP); DML changes data (INSERT, UPDATE, DELETE).

Worked example: CREATE TABLE T(a INT CHECK (a > 0)); INSERT NULL.

Accepted: the check is UNKNOWN, which does not count as a violation.

Now play with it

  • →Read the rule table once, then cover the right column and recite it.
  • →Decide why a view with GROUP BY is not updatable.
TopicRule worth memorising
Viewa stored query; updatable only if it maps to one base table without DISTINCT, GROUP BY, aggregates or joins
PRIMARY KEYunique and NOT NULL, one per table
UNIQUEno duplicates, but NULLs are allowed
NOT NULLcolumn may not be NULL
CHECK (cond)row is accepted if cond is TRUE or UNKNOWN (NULL passes)
FOREIGN KEY … ON DELETE CASCADEchild rows are removed with the parent
Triggerruns automatically before or after INSERT / UPDATE / DELETE
DDL vs DMLCREATE, ALTER, DROP vs INSERT, UPDATE, DELETE, SELECT
DELETE vs TRUNCATE vs DROPDELETE removes chosen rows (can be rolled back); TRUNCATE empties the table; DROP removes the table itself

Check yourself

Which constraint allows NULL values?

Remember: PK = unique and not null; CHECK passes on UNKNOWN; views with aggregates are not updatable.
Exam notes (the dense version)

A view is a stored query; it is updatable only when it maps simply to one base table (no aggregates, DISTINCT, GROUP BY). Constraints: PRIMARY KEY, UNIQUE (allows NULL), NOT NULL, CHECK, FOREIGN KEY … ON DELETE/UPDATE. A trigger runs automatically on INSERT/UPDATE/DELETE. DDL (CREATE, ALTER, DROP) changes the schema; DML (INSERT, UPDATE, DELETE, SELECT) changes or reads data; a CHECK constraint passes when the condition is TRUE or UNKNOWN.

Previous year questions: SQL (13)

PYQs are grouped by topic, so this is the full set for SQL; try the ones that match this subtopic. Open all 13 on the topic page.