Help us understand the problem. What is going on with this article?

【Laravel】 MySQL の生成カラムに NOT NULL 制約を付与する方法

情報更新

Laravel 6.x で以下のプルリクが採用され,フレームワーク標準で対応できるようになりました!

->storedAsNotNull() の代わりに ->storedAs()->nullable(false) を使用してください。


動機

MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.8 CREATE TABLE and Generated Columns

col_name data_type [GENERATED ALWAYS] AS (expression)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

見ての通り,MySQLの生成カラムにはNOT NULL制約を付与することができる。これにより,誤った値の組み合わせが別のカラムに入っているときにNULLになるように生成カラムの式を書くと,柔軟な条件で不整合を防ぐことができる。

database/MySqlGrammar.php at 16980ffecb15e47d0a1942a64d3cc548e22f709c · illuminate/database

    /**
     * Get the SQL for a nullable column modifier.
     *
     * @param  \Illuminate\Database\Schema\Blueprint  $blueprint
     * @param  \Illuminate\Support\Fluent  $column
     * @return string|null
     */
    protected function modifyNullable(Blueprint $blueprint, Fluent $column)
    {
        if (is_null($column->virtualAs) && is_null($column->storedAs)) {
            return $column->nullable ? ' null' : ' not null';
        }
    }

しかしお節介なことに,Laravelでは->virtualAs()または->storeAs()が存在する場合にはNOT NULLを付与してくれない。この問題を解決するために ->virtualAsNotNull() および ->storedAsNotNull() を使えるようにする。

実装

コネクションリゾルバの登録方法が5.4から変更されているので注意。Extending Laravel 5 Blueprint for MySqlConnection の情報は古いです。

(サービスプロバイダの登録は省略)

App/Providers/DatabaseServiceProvider.php
<?php

declare(strict_types=1);

namespace App\Providers;

use App\Database\MySqlConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        Connection::resolverFor('mysql', function (...$parameters) {
            return new MySqlConnection(...$parameters);
        });
    }
}
App/Database/MySqlConnection.php
<?php

declare(strict_types=1);

namespace App\Database;

use App\Database\Schema\Grammars\MySqlGrammar;
use Illuminate\Database\MySqlConnection as BaseMySqlConnection;

class MySqlConnection extends BaseMySqlConnection
{
    /**
     * Get the default schema grammar instance.
     *
     * @return \Illuminate\Database\Grammar
     */
    protected function getDefaultSchemaGrammar()
    {
        return $this->withTablePrefix(new MySqlGrammar());
    }
}
App/Database/Schema/Grammars/MySqlGrammar.php
<?php

declare(strict_types=1);

namespace App\Database\Schema\Grammars;

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Schema\Grammars\MySqlGrammar as BaseMySqlGrammar;
use Illuminate\Support\Fluent;

class MySqlGrammar extends BaseMySqlGrammar
{
    public function __construct()
    {
        if (false !== $position = array_search('VirtualAs', $this->modifiers, true)) {
            array_splice($this->modifiers, $position, 0, ['VirtualAsNotNull']);
        }
        if (false !== $position = array_search('StoredAs', $this->modifiers, true)) {
            array_splice($this->modifiers, $position, 0, ['StoredAsNotNull']);
        }
    }

    /**
     * Get the SQL for a generated virtual column modifier with NOT NULL constraint.
     *
     * @param  Blueprint   $blueprint
     * @param  Fluent      $column
     * @return null|string
     */
    protected function modifyVirtualAsNotNull(Blueprint $blueprint, Fluent $column): ?string
    {
        return $column->virtualAsNotNull !== null ? " as ({$column->virtualAsNotNull}) virtual" : null;
    }

    /**
     * Get the SQL for a generated stored column modifier with NOT NULL constraint.
     *
     * @param  Blueprint   $blueprint
     * @param  Fluent      $column
     * @return null|string
     */
    protected function modifyStoredAsNotNull(Blueprint $blueprint, Fluent $column): ?string
    {
        return $column->storedAsNotNull !== null ? " as ({$column->storedAsNotNull}) stored" : null;
    }
}
mpyw
古い記事はそのまま参考にしないようにご注意ください
synapse
Synapseは、オンラインサロンサービスにおけるパイオニアとして、かつて存在していたスタートアップです。
https://synapseam.github.io/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした