Search code examples
sqlrequired

Required If Dependency in SQL

I need to be able to restrict inserts into a table if a specified column is not provided.

Here's the catch, there is another column which is dependent on that column, that when provided, should enforce the required constraint.

I have looked at CHECK constraints in CREATE TABLE statements, but I do not know how to do an IF THEN statement that will check fieldA, then enforce required on fieldB.

Both columns are nullable and in some cases neither is required, but when fieldA is provided, I want to limit the database to only allow non null values for fieldB.

So for a literal example, here is my table called Person - it has the following columns:

PersonID INT IDENTITY
Name VARCHAR(50) NULL
Age INT NULL 
DateOfBirth VARCHAR(20) NULL

Now, if a record is attempted to be inserted into Person where Age (fieldA) is provided, I want to ensure that DateOfBirth (fieldB) is also provided.

This kind of validation is done from the app, but we also want it on our tables.

Solution

  • You want a check constraint:

    check ((DateOfBirth is null and age is null) or (DateOfBirth is not null and age is not null))

    Note:

    • DO NOT STORE DATES AS STRINGS. Wow. I put that in all-caps.
    • Storing age is a bad idea. It tends to change quite frequently.