MySQL में Updatable Views

Updatable और Insertable अलग हैं

Updatable view data-changing statement का target बन सकती है और change underlying table तक पहुँचता है। Central idea one-to-one row mapping है: MySQL identify कर सके कि view row कौन-सी base row represent करती है।

Verified Orders Lab

DROP TABLE IF EXISTS orders_updatable_lab;
DROP TABLE IF EXISTS customers_updatable_lab;
CREATE TABLE customers_updatable_lab (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(80) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE orders_updatable_lab (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  approved_by VARCHAR(50) NULL
) ENGINE = InnoDB;

INSERT INTO customers_updatable_lab VALUES
(101,'Asha'),(102,'Bilal'),(103,'Charu'),(104,'Deepak');

INSERT INTO orders_updatable_lab
(order_id, customer_id, status, order_date, total_amount, approved_by)
VALUES
(1,101,'PAID','2026-08-01',1200,'Admin'),
(2,101,'PENDING','2026-08-05',500,NULL),
(3,102,'PAID','2026-08-03',750,'Admin'),
(4,103,'CANCELLED','2026-08-04',300,NULL),
(5,101,'PAID','2026-08-10',1500,'Manager');

CREATE OR REPLACE ALGORITHM = MERGE
VIEW pending_orders_v AS
SELECT order_id, customer_id, status,
       order_date, total_amount, approved_by
FROM orders_updatable_lab
WHERE status = 'PENDING';

Simple single-table view each visible row को one base row से map करती है। MERGE-compatible processing updatability support करती है; predicate enforcement अलग concern है।

View से UPDATE और DELETE

UPDATE pending_orders_v
SET total_amount = 550.00
WHERE order_id = 2;

SELECT order_id, total_amount
FROM orders_updatable_lab
WHERE order_id = 2;

Base table changed। CHECK OPTION न होने से status update करके row view से disappear भी कर सकती है:

UPDATE pending_orders_v
SET status = 'PAID', approved_by = 'Manager'
WHERE order_id = 2;

SELECT * FROM pending_orders_v WHERE order_id = 2;

Row base table में PAID status के साथ मौजूद है। यह बिना CHECK OPTION legal है और application को surprise कर सकता है।

START TRANSACTION;
DELETE FROM pending_orders_v WHERE order_id = 2;
ROLLBACK;

Simple merged view से DELETE mapped base row delete करती है। Transactions, permissions और exact predicates रखें।

WITH CHECK OPTION से Predicate Protect

-- Next independent demonstration के लिए lab row reset।
UPDATE orders_updatable_lab
SET status = 'PENDING', approved_by = NULL
WHERE order_id = 2;

CREATE OR REPLACE ALGORITHM = MERGE
VIEW pending_orders_checked_v AS
SELECT order_id, customer_id, status,
       order_date, total_amount, approved_by
FROM orders_updatable_lab
WHERE status = 'PENDING'
WITH CASCADED CHECK OPTION;

UPDATE pending_orders_checked_v
SET total_amount = 575.00
WHERE order_id = 2;

UPDATE pending_orders_checked_v
SET status = 'PAID'
WHERE order_id = 2;

CHECK OPTION predicate fail करने वाला INSERT भी reject करती है। View another view पर बनी हो तो LOCAL/CASCADED recursive scope control करते हैं; omitted default CASCADED है। Single-level में practical predicate check same है।

Updatable View Insertable कब है?

• Duplicate view column names न हों।
• Insert columns simple base-column references हों, expressions नहीं।
• No-default required base columns represented/supplied हों।
• Permitted single base table target हो।
• CHECK OPTION predicate satisfy हो।

INSERT INTO pending_orders_checked_v
(customer_id, status, order_date, total_amount)
VALUES
(104, 'PENDING', '2026-08-15', 800.00);

order_id AUTO_INCREMENT और approved_by nullable है। Expression view distinction दिखाती है:

CREATE VIEW order_amount_display_v AS
SELECT order_id, total_amount,
       total_amount * 1.18 AS amount_with_tax
FROM orders_updatable_lab;

Calculated column complete target को noninsertable बनाती है, पर simple mapped columns update हो सकती हैं जब expression assign न हो।

Nonupdatable बनाने वाले Constructs

CREATE VIEW customer_total_v AS
SELECT customer_id, SUM(total_amount) AS total
FROM orders_updatable_lab
GROUP BY customer_id;

यह report के लिए useful है, update नहीं: total=5000 किस base row में जाए? Read-only property defect नहीं।

Join Views के Nuanced Rules

“Any join read-only” too broad है। Some MERGE-compatible inner-join views updatable हो सकती हैं। लेकिन:

• One UPDATE केवल one underlying table की columns modify करे।
• INSERT stricter और permitted single table में हो।
• DELETE join view को target allow नहीं करता।
• Outer joins/nonupdatable/materialized components additional limits लाते हैं।

CREATE VIEW order_customer_join_v AS
SELECT o.order_id, o.total_amount,
       c.customer_id, c.customer_name
FROM orders_updatable_lab AS o
JOIN customers_updatable_lab AS c
  ON c.customer_id = o.customer_id;

Appearance से legality infer न करें। Exact statement की official rules, metadata और deployed version पर transaction test लें।

Verify और Safe Checklist

SELECT TABLE_NAME, IS_UPDATABLE, CHECK_OPTION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN
      ('pending_orders_v','pending_orders_checked_v',
       'customer_total_v');

SHOW CREATE VIEW pending_orders_checked_v;

1. One-to-one mapping और IS_UPDATABLE confirm करें।
2. UPDATE/DELETE को INSERT से अलग evaluate करें।
3. Rows predicate में रहनी हों तो CHECK OPTION add करें।
4. Base NOT NULL, CHECK, UNIQUE, FK constraints रखें।
5. Only required write privileges दें।
6. Allowed/rejected boundaries transaction में test करें।
7. Affected rows और audit check करें।
8. Multi-table write view तभी जब semantics documented हों।
9. Definition/app code together version करें।

पहले views tutorial review करें, फिर stored procedures पढ़ें।

Official संदर्भ

• MySQL 8.4: Updatable and Insertable Views
• MySQL 8.4: WITH CHECK OPTION
• MySQL 8.4: INFORMATION_SCHEMA VIEWS

One-to-one mapping, insertability, join limitations, metadata और CHECK OPTION official MySQL 8.4 manual से verify किए गए हैं।