Support check constraints

[jaulz]

This is actually a feature request and it would be cool to use Postgres' check constraints. text and json(b) fields are (almost) unlimited by default so that would be a nice addition to this library's Schema. Not sure if that means that we need to come up with a new type or if the existing field definitions can be enhanced.

[tpetry]

Do you mean normal check constraints, e.g. CHECK (LENGTH(textcol) < 1000), or something else?

I've got a stashed implementation which is 80% done, but hadn't the time to finish it. But how do you want to limit the JSONB size? Something like CHECK(LENGTH(textcol::text) < 10000)?

[jaulz]

Yep, that's exactly what I had in mind 😊 And maybe as a second step one could use DB::raw() to even impose other checks.

[tpetry]

Oh, my current syntax is already much nicer than DB::raw(). It's like that if I remember correctly:

$table->jsonb('attributes')->check('LENGTH(textcol::text) < 10000');
$table->check('somethingthing', 'LENGTH(textcol::text) < 10000');

So you get the full power of SQL check constraints, but no specific shortcuts for limiting text/json length.

[jaulz]

Wow, that's exactly what I am looking for! Then maybe as a cherry on top: treat numbers as length checks automatically 😉 Would you have the time to add it or would you like to push your stashed work and I can continue the work?

[tpetry]

I'll work at it after #21. I don't know whether any magic would result in problems in some cases. It's always so hard to predict.

[jaulz]

Great, thanks a lot!

[tpetry]

Exclusion constraints should also be supported:

ALTER TABLE my_table
  ADD CONSTRAINT no_overlap 
  EXCLUDE USING gist (product_id WITH =, applicable_period WITH &&)
  WHERE (user_id is null);

[jaulz]

That's a good one as well. I didn't know that one so thanks for the hint 😊 Though, that sounds like it should be handled with a separate method?

// EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ]
$table->exclude([
  'index' => 'gist', // short-hand for: 'index' => [ 'method' => 'gist', 'parameter' => '' ]
  'elements' => [
    'product_id' => '=',
    'applicable_period' => '&&'
   ],
   'where' => 'user_id is null'
]);

[alancolant]

@tpetry I am also interested in this feature. Have you made any progress on the development?