How to fix ERROR 1368: CHECK OPTION on non-updatable view in MySQL

SELECT TABLE_NAME, IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'your_view_name';

-- Non-updatable: has aggregate function
CREATE VIEW non_updatable_view AS
SELECT department, COUNT(*) as emp_count, SUM(salary) as total_salary
FROM employees
GROUP BY department;

-- Non-updatable: has DISTINCT
CREATE VIEW distinct_view AS
SELECT DISTINCT city
FROM employees;

-- Non-updatable: has UNION
CREATE VIEW union_view AS
SELECT id, name FROM employees
UNION
SELECT id, name FROM contractors;

-- Non-updatable: has subquery in SELECT
CREATE VIEW subquery_view AS
SELECT id, name, (SELECT COUNT(*) FROM orders WHERE orders.emp_id = employees.id) as order_count
FROM employees;

CREATE VIEW sales_summary AS
SELECT
  department,
  COUNT(*) as total_sales,
  SUM(amount) as total_revenue
FROM orders
GROUP BY department
WITH CHECK OPTION;  -- ERROR 1368!

CREATE VIEW sales_summary AS
SELECT
  department,
  COUNT(*) as total_sales,
  SUM(amount) as total_revenue
FROM orders
GROUP BY department;
-- No WITH CHECK OPTION needed since this is for reporting only

-- Non-updatable (has GROUP BY and COUNT)
CREATE VIEW order_summary AS
SELECT product_id, COUNT(*) as num_orders
FROM orders
GROUP BY product_id
WITH CHECK OPTION;  -- ERROR 1368

-- Solution: Create an updatable view instead
CREATE VIEW active_orders AS
SELECT id, product_id, amount, status
FROM orders
WHERE status = 'active'
WITH CHECK OPTION;  -- Now works!

-- Non-updatable
CREATE VIEW unique_cities AS
SELECT DISTINCT city
FROM employees
WITH CHECK OPTION;  -- ERROR 1368

-- Solution: Select all columns (may need additional column for identity)
CREATE VIEW employee_locations AS
SELECT id, city
FROM employees
WITH CHECK OPTION;  -- Works

-- Non-updatable (subquery in SELECT)
CREATE VIEW emp_with_count AS
SELECT id, name, (SELECT COUNT(*) FROM orders WHERE orders.emp_id = employees.id) as order_count
FROM employees
WITH CHECK OPTION;  -- ERROR 1368

-- Solution: If you need this data, query the tables separately
-- Or create a trigger to maintain a denormalized column

CREATE VIEW employee_departments AS
SELECT e.id, e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WITH CHECK OPTION;  -- May fail depending on structure

CREATE VIEW employee_departments AS
SELECT e.id, e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WITH CHECK OPTION;  -- More likely to work

CREATE ALGORITHM = TEMPTABLE VIEW my_view AS
SELECT * FROM base_table
WITH CHECK OPTION;  -- ERROR 1368

CREATE ALGORITHM = MERGE VIEW my_view AS
SELECT * FROM base_table
WHERE active = 1
WITH CHECK OPTION;  -- Works

CREATE VIEW my_view AS
SELECT * FROM base_table
WHERE active = 1
WITH CHECK OPTION;  -- MySQL automatically uses MERGE if possible

SELECT TABLE_NAME, DEFINER, SECURITY_TYPE, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_view_name';

-- Create the base view (without CHECK OPTION)
CREATE VIEW order_details_view AS
SELECT id, product_id, quantity, price, created_at
FROM order_items;

-- Create INSTEAD OF INSERT trigger
DELIMITER //
CREATE TRIGGER order_items_insert
INSTEAD OF INSERT ON order_details_view
FOR EACH ROW
BEGIN
  -- Validation logic
  IF NEW.quantity <= 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Quantity must be greater than 0';
  END IF;

  IF NEW.price < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Price cannot be negative';
  END IF;

  -- Insert the validated data
  INSERT INTO order_items (product_id, quantity, price, created_at)
  VALUES (NEW.product_id, NEW.quantity, NEW.price, NEW.created_at);
END //
DELIMITER ;

-- Create INSTEAD OF UPDATE trigger
DELIMITER //
CREATE TRIGGER order_items_update
INSTEAD OF UPDATE ON order_details_view
FOR EACH ROW
BEGIN
  IF NEW.quantity <= 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Quantity must be greater than 0';
  END IF;

  UPDATE order_items
  SET quantity = NEW.quantity, price = NEW.price
  WHERE id = NEW.id;
END //
DELIMITER ;

-- Drop the old view if it exists
DROP VIEW IF EXISTS your_view_name;

-- Create the corrected view
CREATE VIEW your_view_name AS
SELECT id, name, status
FROM base_table
WHERE status != 'deleted'
WITH CHECK OPTION;

-- Verify updatability
SELECT IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'your_view_name';
-- Should return 'YES'

INSERT INTO your_view_name (id, name, status)
VALUES (1, 'Test Record', 'active');

UPDATE your_view_name
SET name = 'Updated Name'
WHERE id = 1;

-- This should fail because it violates the WHERE clause
UPDATE your_view_name
SET status = 'deleted'
WHERE id = 1;
-- With CHECK OPTION, this fails because the row would become invisible