🔍 Oracle SQL: WITH CHECK OPTION & Inline Views Explained

In Oracle SQL, WITH CHECK OPTION is used with views to ensure data modifications through the view adhere to the view’s WHERE clause. Inline Views are subqueries in the FROM clause that act like temporary tables in a query.

✔️ 1. WITH CHECK OPTION

Purpose:

Ensures any INSERT or UPDATE on a view does not violate the view’s filter condition.

Syntax:

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition
WITH CHECK OPTION;

Example:

CREATE VIEW HIGH_SALARY_EMP AS
SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > 50000
WITH CHECK OPTION;

Behavior:

If you try to INSERT or UPDATE a row through the view that doesn’t meet SALARY > 50000, Oracle raises an error.

---

🔎 2. Inline Views

Definition:

An inline view is a subquery in the FROM clause treated like a temporary table for the duration of the query.

Syntax:

SELECT alias.column1, alias.column2
FROM (
  SELECT columns
  FROM table_name
  WHERE condition
) alias
WHERE alias.column1 = some_value;

Example:

SELECT dept_stats.DEPT_ID, dept_stats.AVG_SALARY
FROM (
  SELECT DEPT_ID, AVG(SALARY) AS AVG_SALARY
  FROM EMPLOYEE
  GROUP BY DEPT_ID
) dept_stats
WHERE dept_stats.AVG_SALARY > 60000;

---

📘 Use WITH CHECK OPTION to maintain data integrity through views, and Inline Views to simplify complex queries.

Trust Coding : Programming