3

I am trying to build a table of this structure using Laravel's migrations feature:

------------------------------------
| Data  | Rules | Allow | Restrict |
------------------------------------
| item1 | rule1 | 1,3   |   null   |
| item2 | rule2 | null  |  2,5,6   |
------------------------------------

As in, for each entry either Allow or Restrict must possess a not null value, but not both. I've found this comment which sounds like the condition I need, but I need to express it in a format understandable to Laravel.

Ivan T.
  • 525
  • 5
  • 19
  • Is there a condition at which both `allow` and `restrict` would both have values? – Ohgodwhy Apr 16 '18 at 07:27
  • @Ohgodwhy There isn't. Only one of them should have a value within a row. – Ivan T. Apr 16 '18 at 07:28
  • I think you should fix this in your model and use the insert event. – online Thomas Apr 16 '18 at 07:46
  • @ThomasMoors So if I understood your suggestion right, the check should be performed on the PHP level and not by the database engine I'm using? I could see it working, but I'm curious whether it's possible to add such a constraint via migrations. – Ivan T. Apr 16 '18 at 07:52
  • Maybe you can give them nullable and fix the solution in the frontend. Just think these are checkboxes. True or False. But of course this could be a temporary solution. – Ali Özen Apr 16 '18 at 08:00
  • Make a proper data model. Having an inapplicable null as a value in a table is very bad. – apokryfos Apr 16 '18 at 08:01
  • 1
    Migrations add nothing new to the table addressing the limitations of SQL. It just is a means to make structural changes visible in version control and be able to revert them when needed. – online Thomas Apr 16 '18 at 08:48

1 Answers1

1

I think there are 2 good solutions

  1. Seperate the data in 2 tables

Table1 -> data, rules, constraint (FK)

Table2-> id (PK, referenced by Table1), content (allow/restrict numbers), isAllow(bool)

That way you do the constraint in the database. This is the better solution, because now you don't have null values in your database. Normalisation is a good thing.

  1. Use the event listener to check before insert

https://laravel.com/docs/5.6/eloquent#events

public function creating(Table1 $t1)
{
       // check if 1 is null and 1 is not null before creating
}
online Thomas
  • 8,864
  • 6
  • 44
  • 85