Laravel Migration Nullable Based on Another Column

Question

I'm writing a DB migration using Laravel and I'm wondering if I can set a column as not nullable based on the value of another column.

For example:

If    User.owns_cat === true  
Then  User.cat_name->nullable(false)

I know I can handle this via validation rules later but would like to have this rule at the DB level.

Answer 1

What you really need is support for conditional functional dependencies and association rules.

Association Rules (AR)

c => f(A)

where c is a logically determinable value, which, if met, then the column set A will fullfill something where

A = (A1, A2, ..., An)

Conditional Functional Dependencies (CFD)

c => A -> B

where c is a logically determinable value, which, if met, then the column set A determines the values of the column set B, where

A = (A1, A2, ..., An)

and

B = (B1, B2, ..., Bn)

Problem

RDBMS systems do not tend to support ARs or CFDs out of the box, at least that was the case the last time I checked. So, since the underlying system probably does not support the feature you need, Laravel migrations will probably not achieve that either.

Solutions

The problem with defining schema based on application code is that this task is not quite appropriately done at the ORM level of the tech stack, because:

• you use some schema generator you can only indirectly influence
• more complex problems are very difficult to be solved via such a system
• the schema change generator might have bugs, which adds unnecessarily a new level of worry to your problems

So, what you can do:

• you can implement a functionality on application-level that checks some condition, which, if met, enforces the rule, that is, if the rule is to be violated, then either throws an error or sets a default, while if the rules are met, then calls set
• you can implement a trigger in the database, which, before insert/update would check and enforce the rules you need
• schema solution
• periodically running data fix

Trigger

You would create a trigger, which would check the value of column2 and if your condition is met, check whether column1 would be null. If so, then you might chose to throw an error or set a default.

Advantage: Your data consistency will be maintained even if write operations happen outside your application.

Disadvantage: This will be quite difficult to maintain if the rules change frequently and this would not have access on your application-level resources.

Whether this approach is ideal for you, depends on your needs.

Schema Solution

So, if c1 has some value, then c2 is not nullable. You can move c1 to another table and make c2 a foreign key. If your condition for c1 is met, ensure that c2 is a proper foreign key to the new table which holds c1.

Advantage: You use only resources the RDBMS provides you out of the box

Disadvantage: This approach is counter-intuitive if your condition for c2 is complex

The applicability of this approach depends on the complexity of your condition.

Periodically Running Data Fix

You can allow temporary inconsistencies with your rules and solve the issues automatically, in a periodical manner.

Advantages: The solution might be a single request to the database server, which would execute a stored procedure or something of the like, performing the tasks quickly.

Disadvantages: You cannot rely on your rules being enforced at every record at every moment.

This is applicable if you do not worry about your rule being temporarily breached as long as it ends up being correct fairly quickly.

Application-Level Support

You can implement a class which has a set method. That set method will get an entity and a function that returns a boolean. If the function returns true, then call set. Otherwise either throw an exception or set a default.