数据库CRUD操作用户追踪：外键与CHECK CONSTRAINT选型及设计合理性咨询

Short answer: No, replacing all employeeID foreign keys with CHECK constraints is not a reasonable strategy—you’re mixing up two tools that solve entirely different problems. Let’s break this down clearly:

1. CHECK Constraints can’t replace Foreign Keys for data integrity #

Your loadUser field exists to link records to a valid employee in the employees table. Foreign keys are specifically designed to enforce this cross-table consistency:

• They ensure every loadUser value maps to a real employeeID (no "ghost user" entries in your tables).
• They handle cleanup if an employee is removed (via ON DELETE rules like RESTRICT to prevent deleting employees with linked data, or SET NULL if that makes sense for your business).

CHECK constraints can’t do this reliably. Most databases (like MySQL, SQL Server) don’t let CHECKs reference other tables—you can’t write CHECK (loadUser IN (SELECT employeeID FROM employees)) and have it work as real-time validation. Even in databases that allow this via functions (like PostgreSQL), it’s inefficient and won’t automatically update if the employees table changes (e.g., deleting an employee won’t flag old records with their ID as invalid). You’ll end up with dirty, inconsistent data fast.

2. CHECK Constraints are the wrong tool for permission control #

Your business rules are about who can perform which actions on which data—this is access control, not data validation. CHECK constraints are static: they only validate field values at insert/update time, but they can’t:

• Identify the current user performing the CRUD operation (you can’t pull the active user’s ID inside a CHECK to compare against loadUser or check VIP status).
• Distinguish between operation types (e.g., allow inserts but block updates for non-VIP users).
• Adapt easily to rule changes (if your VIP criteria shifts later, you’ll have to rewrite CHECKs across every table—nightmare maintenance).

The Right Approach: Separate Data Integrity and Access Control #

You need to handle these two requirements with the right tools:

Keep Foreign Keys for Data Integrity #

Don’t remove those employeeID foreign keys—they’re the only reliable way to ensure your loadUser values stay linked to real employees. This is non-negotiable for a healthy database.

Use Database Roles + Triggers for Permission Rules #

Tailor your solution to each business rule:

• Tables open to any user: Assign UPDATE permissions to all database user roles. Simple, straightforward.
• VIP-only tables: Create a VIP_User database role, grant UPDATE permissions only to this role, and assign eligible employees to it.
• Tables restricted to the original entry user: Use a trigger to validate the current user against the record’s loadUser before allowing updates. Here’s a quick example for PostgreSQL:

  CREATE OR REPLACE FUNCTION validate_owner_update()
  RETURNS TRIGGER AS $$
  BEGIN
    -- Assume your database username matches employeeID
    IF current_user::TEXT != OLD.loadUser THEN
      RAISE EXCEPTION 'Only the user who created this record can modify it.';
    END IF;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER trigger_validate_owner
  BEFORE UPDATE ON restricted_table
  FOR EACH ROW EXECUTE FUNCTION validate_owner_update();
  

Bonus: Combine with Application Layer Controls #

For maximum flexibility, you can handle some permission checks in your application (e.g., checking user roles before sending a CRUD request) and use database-level controls as a safety net to prevent direct database tampering.

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