数据完整性保障层级、SQL表非空验证责任及跨层契约问询

Great questions—these are exactly the kind of layer responsibility questions that keep backend teams aligned and systems reliable. Let’s break them down clearly:

Data integrity isn’t the job of a single layer—it’s a defense-in-depth approach, but each layer has a primary role:

• Database Layer: This is your first and most critical line of defense. It’s responsible for enforcing entity integrity (like primary keys, non-null constraints) and referential integrity (foreign keys). Databases are built to enforce these rules consistently, even if other layers fail. For example, setting a NOT NULL constraint on your employee field here guarantees the database will reject any attempt to insert or update a row with a null value.
• Business Logic Layer: This layer handles domain-specific integrity rules that go beyond basic database constraints. For example, ensuring an employee’s name meets your company’s naming standards (no special characters, minimum length) or that an employee is assigned to a valid department. It also acts as a safety net—if for some reason a database constraint is bypassed (e.g., a legacy script without validation), this layer can catch invalid data before it propagates.
• API/Application Layer: This layer focuses on input validation for external requests. It ensures that data coming from the frontend or external services meets basic format requirements before it even reaches the business logic or database. For example, returning a clear error to the frontend if they try to send a null employee value in a create request, instead of letting it fail silently at the database.

Let’s tackle each part of this question:

Who Ensures the employee Field is Non-Null When the Frontend Requests Data? #

Again, it’s a layered approach, but here’s the breakdown:

• First: Database Layer: If your schema defines employee as NOT NULL, the database should never return a null value for this field. This is the foundation.
• Second: Business Logic Layer: Even with database constraints, it’s smart to add a quick check here when fetching data. Why? Because mistakes happen—maybe someone accidentally drops the NOT NULL constraint, or you have legacy data that predates the constraint. Catching it here prevents null values from reaching the API/frontend, avoiding unexpected errors downstream.
• Third: API Layer: As the final step before sending data to the frontend, you can add a last-minute validation or normalization (e.g., replacing any unexpected null with a default like "Unknown Employee") to ensure the frontend always gets a valid value.

Should Unit Tests for Data Reading Cover the "Null employee" Scenario? #

Absolutely—even if you think it’s impossible. Here’s why:

• Legacy Data: If your table existed before you added the NOT NULL constraint, there might be old rows with null values that haven’t been cleaned up.
• Human Error: Someone could accidentally modify the schema and remove the constraint, or run a script that bypasses validation and inserts nulls.
• Robustness: Testing edge cases like this ensures your code doesn’t crash or behave unpredictably if the unexpected happens. It’s cheap insurance against future headaches.

For example, you could write a unit test that mocks a database response with a null employee value and verifies that your business logic layer throws an error or normalizes the value appropriately.

How to Handle Contracts Between Layers? #

Clear contracts between layers prevent misalignment and reduce bugs. Here’s how to approach it:

• Database ↔ Business Logic: Define an explicit contract: "The employee field will never be null." Your business logic can generally trust this, but add a defensive check (as mentioned earlier) to handle exceptions. Also, add monitoring or alerts to notify you if the database constraint is modified or violated.
• Business Logic ↔ API: The contract here is: "The business logic will only pass non-null employee values to the API." The API can rely on this, but again, a quick sanity check doesn’t hurt. If a null slips through, the API should handle it gracefully (e.g., log the error and return a 500 or normalized value).
• API ↔ Frontend: The contract for the frontend is: "The API response will always include a non-null employee value." This lets the frontend team build without handling null cases for this field, reducing their complexity. If an error occurs (e.g., a null slips through), the API should return a clear error message instead of a broken response.

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