Create a View, Then Update Through It

This lesson puts the updatability rule from an earlier lesson into practice end to end: build a view that actually qualifies as updatable, then write a real UPDATE through it, and confirm the change landed on the base table. Everything here uses standard Oracle syntax throughout; no SQL Server or other vendor-specific syntax is mixed in anywhere in this lesson, since doing so would misrepresent how any single one of these engines actually behaves.

Twenty years ago, courses covering "advanced SQL" often meant a specific product's advanced SQL, and the specific product varied by which classroom you happened to sit in. This lesson, and this course generally, treats Oracle as that specific product throughout, which matters because the updatability rules, trigger syntax, and even some of the data types covered across this module genuinely differ from one engine to the next; mixing vendor conventions within a single lesson would teach syntax that runs correctly nowhere at all.

Start with a table and some sample data:

CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    Department VARCHAR2(50),
    Salary NUMBER(10, 2)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (1, 'Alice', 'Johnson', 'HR', 60000.00);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (2, 'Bob', 'Smith', 'IT', 75000.00);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (3, 'Carol', 'Lee', 'IT', 80000.00);

A view showing only the IT department:

CREATE VIEW IT_Employees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Department = 'IT';

This view qualifies as updatable under the same rule covered in an earlier lesson: it's built on a single table, includes the table's primary key (EmployeeID), and contains no aggregate functions, DISTINCT, GROUP BY, or joins. Every condition that would disqualify a view, covered in depth already, is absent here. Notice, too, that Department itself isn't part of the view's SELECT list, only used in its WHERE clause; a column doesn't need to be displayed by a view to be used for filtering, and this particular omission becomes relevant again later in this lesson.

UPDATE IT_Employees
SET Salary = Salary * 1.10
WHERE EmployeeID = 2;

This raises Bob's salary by 10%, exactly as if the same UPDATE had been issued directly against Employees. Confirming the change means checking the base table:

SELECT * FROM Employees WHERE EmployeeID = 2;

EMPLOYEEID  FIRSTNAME  LASTNAME  DEPARTMENT  SALARY
2           Bob        Smith     IT          82500.00

Bob's salary now reads 82500.00, up from 75000.00. The view had no data of its own to update; the write passed straight through to Employees, which is the only place the value ever actually lived. Querying the view itself afterward confirms the same thing from the other direction:

SELECT * FROM IT_Employees WHERE EmployeeID = 2;

EMPLOYEEID  FIRSTNAME  LASTNAME  SALARY
2           Bob        Smith     82500.00

The updated value shows up through the view immediately, with no separate refresh step, for the same reason covered in an earlier lesson on how views execute: IT_Employees re-runs its own query fresh every time it's referenced, so it can never show anything other than the current state of Employees.

As a quick recap of what would have disqualified this view from supporting that UPDATE at all: any aggregate function (SUM, AVG, and similar), DISTINCT, a GROUP BY or HAVING, a set operation like UNION, or a join without an INSTEAD OF trigger routing the write, each covered in earlier lessons, would have made IT_Employees read-only instead.

Everything established for UPDATE applies identically to INSERT and DELETE against the same view, since the underlying question, is there one unambiguous base table row this operation maps to, doesn't change based on which of the three operations is being performed.

INSERT INTO IT_Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (4, 'David', 'Kim', 72000.00);

This inserts a new row directly into Employees, not into any storage belonging to the view. Because IT_Employees filters to Department = 'IT' but the INSERT above never supplies a Department value at all, the new row lands in Employees with Department set to NULL, which means it wouldn't actually appear if IT_Employees were queried again immediately afterward; NULL doesn't satisfy Department = 'IT'. This is exactly the kind of surprising edge case WITH CHECK OPTION, covered in an earlier lesson, exists to prevent, by rejecting a write that would produce a row the view's own condition wouldn't select.

Deleting through the view works the same way:

DELETE FROM IT_Employees WHERE EmployeeID = 4;

This removes the row from Employees entirely, not just from whatever IT_Employees happens to display. There's no version of "delete from the view but leave it in the underlying table"; a view has no separate storage to delete from in the first place.

It's worth noticing what DELETE FROM IT_Employees can and can't reach, too. Since the view's WHERE clause already restricts it to Department = 'IT', a DELETE issued against IT_Employees can never remove an HR or any other non-IT employee, regardless of what condition follows it, simply because those rows were never part of the view's result set to begin with. In that sense, IT_Employees doubles as a scoping mechanism for writes as well as reads: granting write access to the view rather than to Employees directly limits not just what a user can see, but what they can delete or modify, to the IT department specifically.

The primary key requirement is worth seeing demonstrated directly, since it's easy to state as a rule without seeing what actually changes when it's violated. Suppose a PhoneNumbers table has CustomerID as its primary key alongside a PhoneNumber column, and a view is built that leaves the key out:

CREATE VIEW MyView AS
SELECT PhoneNumber FROM PhoneNumbers;

This view is read-only. Without CustomerID in the result, there's no way to identify which specific row a given PhoneNumber value came from, so an UPDATE against MyView would have no unambiguous row to target. Adding just that one column back in changes the outcome completely:

CREATE VIEW MyView AS
SELECT CustomerID, PhoneNumber FROM PhoneNumbers;

Nothing else about the view changed, no join was added, no aggregate introduced, yet this version is updatable and the previous one wasn't. The single deciding factor was whether the key needed to uniquely identify a row was actually present in the view's own column list.

It's worth being precise about what actually happens if an UPDATE is attempted against the read-only version. It isn't that Oracle attempts the write and produces an unpredictable result; the database recognizes at parse time that MyView in its key-less form doesn't qualify as updatable at all, and rejects the statement outright with an error before ever touching a single row. That's a meaningfully different, and safer, failure mode than silently updating the wrong row or updating every row that happens to share the same PhoneNumber value.

The INSERT example above, where a new row silently failed to appear in IT_Employees afterward because its Department came through as NULL, is exactly the scenario WITH CHECK OPTION addresses, covered fully in an earlier lesson on views generally. Adding it to IT_Employees closes that gap:

CREATE OR REPLACE VIEW IT_Employees AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Department = 'IT'
WITH CHECK OPTION CONSTRAINT it_employees_dept_ck;

This specific view, though, can't actually enforce that check, since Department isn't part of the view's own SELECT list at all; WITH CHECK OPTION can only validate conditions against columns the view itself exposes. This is worth sitting with for a moment: IT_Employees filters on Department in its WHERE clause but doesn't display that column, which means there's no way for a write against the view to even attempt to set Department to something other than 'IT', and also no way for WITH CHECK OPTION to guard against the NULL-department scenario demonstrated above, since that scenario arises from a column the INSERT never mentioned, not one the check option could intercept. The practical lesson here: WITH CHECK OPTION protects against a write that explicitly tries to violate the view's condition; it doesn't protect against a write that simply omits a required column and lets it default to NULL. Preventing that specific gap would require Department to have a NOT NULL constraint with no default on the base table itself, a base-table-level safeguard, not a view-level one.

To see WITH CHECK OPTION actually working, the filtered column needs to be part of the view's own output. Adding Department to the SELECT list changes the picture entirely:

CREATE OR REPLACE VIEW IT_Employees AS
SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE Department = 'IT'
WITH CHECK OPTION CONSTRAINT it_employees_dept_ck;

Now an attempt to move someone out of the IT department through the view is rejected outright:

-- Rejected: this row wouldn't satisfy Department = 'IT' afterward
UPDATE IT_Employees
SET Department = 'HR'
WHERE EmployeeID = 2;

Since Department is now visible to the view, WITH CHECK OPTION can actually see that this update would produce a row failing the view's own WHERE condition, and blocks it before it happens. The difference between this version and the one before it isn't the presence of WITH CHECK OPTION itself; it's whether the column the check needs to inspect is something the view actually exposes in the first place.

Separately from updatability, it's worth knowing that some database tools let you further restrict how much of a view's own definition other users can see, useful when the schema itself, not just the data, is considered sensitive. The specifics of that kind of protection vary by product and are outside the scope of this lesson; the point worth carrying forward is simply that controlling who can query a view's data and controlling who can see how that view is built are two separate concerns, not the same protection.

Assuming any single-table view is automatically updatable. A single table is necessary but not sufficient; the primary key still has to be present in the view's column list, and no aggregate function, DISTINCT, or GROUP BY can be involved. IT_Employees qualifies on all counts; a superficially similar view missing just the key does not.

Expecting WITH CHECK OPTION to catch every bad write. As demonstrated above, it only guards against a write that explicitly tries to produce a row falling outside the view's condition. A write that omits a column entirely and lets it default to NULL isn't something WITH CHECK OPTION was designed to catch; that's a job for a NOT NULL constraint on the base table itself.

Forgetting that DML through a view has no separate storage to fall back on. Deleting through a view deletes from the base table, permanently, exactly the same as deleting from that table directly. There's no intermediate, safer layer a view provides against an accidental DELETE; the same caution that applies to any DELETE statement applies here without modification.

Confusing "the view filters on a column" with "the view exposes that column." IT_Employees's original definition filtered on Department without ever including it in the SELECT list, which is exactly what made the earlier WITH CHECK OPTION attempt ineffective. A column driving a view's WHERE clause and a column appearing in its output are two independent choices, and confusing the two is an easy way to build a check option that looks correct on paper but silently does nothing.

Looking Ahead

A few points from this lesson worth carrying forward:

• A view built on a single table, including its primary key, with no aggregation, DISTINCT, GROUP BY, or joins, supports INSERT, UPDATE, and DELETE directly, passing each write straight through to the base table.
• The primary key's presence in the view's column list is the deciding factor between a read-only view and an updatable one; nothing else about MyView's two versions changed.
• An attempted write against a non-updatable view fails immediately, at parse time, rather than producing an ambiguous or silently incorrect result.
• WITH CHECK OPTION only guards against a write that would violate a condition on a column the view actually exposes; it can't protect a column the view filters on internally but never displays.
• INSERT, UPDATE, and DELETE through a view all follow the identical underlying rule; there's no separate updatability standard for one operation versus another.

Complete the exercise below to build a view of your own and confirm you can update through it.
Update View - Exercise