Laravel Eloquent如何使用重复的NULL创建UNIQUE约束

问题描述

我是使用MS Sql Server 2014的usinq Laravel 5.我想创建一个唯一约束，但是它应该允许多个空值.

I'm usinq Laravel 5 with MS Sql Server 2014.I want to create a unique constraint but it should allow multiple null values.

这是我正在使用的代码.如果不为null，则"passport_no"必须唯一.

Here is code I'm using. Where 'passport_no' has to be unique if not null.

Schema::create('UserProfile', function(Blueprint $table){
    $table->increments('userprofile_id');
    $table->integer('user_id')->unsigned();
    $table->string('passport_no', 50)->unique()->nullable();

    $table->foreign('user_id')->references('id')->on('users')
            ->onUpdate('cascade')->onDelete('cascade');
});

推荐答案

这是一个古老的问题，但窗台需要回答.如上所述，2008年起的SQL Server(包括Azure SQL)支持可以解决的特殊索引.在数据库迁移中，您可以检查使用的驱动程序，并用特定于MSSQL的语句替换数据库构建器标准SQL.

This is an ancient question, but sill needs answering. As stated above, SQL Server from 2008, including Azure SQL, supports a special index that will work around it. In your database migration you can check the driver used and substitute the database builder standard SQL with an MSSQL-specific statement.

此迁移示例适用于Laravel 5+，并使用唯一但可为空的 api_token 列创建一个users表:

This migration example is for Laravel 5+, and creates a users table with a unique, but nullable, api_token column:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->timestamps();

        $table->string('name', 100)->nullable()->default(null);
        // etc.

        $table->string('api_token', 80)->nullable()->default(null);

        if (DB::getDriverName() !== 'sqlsrv') {
            $table->unique('api_token', 'users_api_token_unique');
        }
    });

    if (DB::getDriverName() === 'sqlsrv') {
        DB::statement('CREATE UNIQUE INDEX users_api_token_unique'
           . ' ON users (api_token)'
           . ' WHERE api_token IS NOT NULL');
    }
}

这篇关于Laravel Eloquent如何使用重复的NULL创建UNIQUE约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！