能否仅为表的更新操作创建CHECK约束，插入新行时忽略该约束？

Great question! The standard CHECK constraint in most relational databases applies to both INSERT and UPDATE operations by default—so you can’t directly configure it to ignore inserts out of the box. But there are reliable workarounds to achieve exactly what you want: making the data_konca >= data_rozp check only run when updating rows.

The Best Approach: Use a Trigger #

Triggers let you define custom logic that runs only for specific operations (like UPDATE), making them perfect for this scenario. Here’s how to implement it in two common databases:

For PostgreSQL #

1. First, create a trigger function that checks the condition only during updates:

CREATE OR REPLACE FUNCTION check_update_date_constraint()
RETURNS TRIGGER AS $$
BEGIN
  -- Only enforce the check when updating a row
  IF TG_OP = 'UPDATE' THEN
    IF NEW.data_konca < NEW.data_rozp THEN
      RAISE EXCEPTION 'Error: data_konca cannot be earlier than data_rozp when updating';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. Then attach this function to your table as a BEFORE UPDATE trigger:

CREATE TRIGGER daty_chk_trigger
BEFORE UPDATE ON your_table_name
FOR EACH ROW EXECUTE FUNCTION check_update_date_constraint();

For SQL Server #

1. Create a trigger that validates the condition only during updates:

CREATE TRIGGER daty_chk_trigger
ON your_table_name
BEFORE UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  
  -- Check if any updated rows violate the condition
  IF EXISTS (
    SELECT 1 FROM inserted
    WHERE data_konca < data_rozp
  )
  BEGIN
    RAISERROR('Error: data_konca cannot be earlier than data_rozp when updating', 16, 1);
    ROLLBACK TRANSACTION;
  END;
END;

Why Not a Conditional CHECK Constraint? #

You might wonder if you can add a condition to the CHECK constraint to skip inserts, like:

-- This won't work in most databases!
ALTER TABLE your_table_name
ADD CONSTRAINT daty_chk
CHECK (data_konca >= data_rozp OR /* logic to detect insert */);

Unfortunately, standard CHECK constraints are row-level and can’t access context about the current operation (insert vs update) or reference other rows in the table. This makes triggers the only reliable cross-database solution.

内容的提问来源于stack exchange，提问作者maciejka