How to fix 44000: with_check_option_violation in PostgreSQL

-- Check the view definition
\d+ view_name

-- Or query the system catalog
SELECT definition FROM pg_views WHERE viewname = 'view_name';

-- Example: View filters for department = 'Sales'
CREATE VIEW sales_employees AS
SELECT * FROM employees
WHERE department = 'Sales'
WITH CHECK OPTION;

-- This will fail (department is 'Marketing', not 'Sales')
INSERT INTO sales_employees (name, department, salary)
VALUES ('John', 'Marketing', 50000);

-- This will succeed (department matches the view condition)
INSERT INTO sales_employees (name, department, salary)
VALUES ('John', 'Sales', 50000);

-- This fails because the update would violate the view condition
UPDATE sales_employees
SET department = 'Marketing'
WHERE name = 'John';

-- This succeeds (department stays 'Sales')
UPDATE sales_employees
SET salary = 60000
WHERE name = 'John';

-- CASCADED (default): checks all underlying view conditions
CREATE VIEW my_view AS
SELECT * FROM parent_view
WHERE additional_condition = true
WITH CASCADED CHECK OPTION;

-- LOCAL: checks only this view's condition, not parent views
CREATE VIEW my_view AS
SELECT * FROM parent_view
WHERE additional_condition = true
WITH LOCAL CHECK OPTION;

-- Instead of inserting through the view:
-- INSERT INTO sales_employees (...) VALUES (...);

-- Insert directly into the base table:
INSERT INTO employees (name, department, salary)
VALUES ('John', 'Marketing', 50000);

-- Drop and recreate without WITH CHECK OPTION
DROP VIEW view_name;

CREATE VIEW view_name AS
SELECT * FROM base_table
WHERE condition = true;
-- No WITH CHECK OPTION clause