Laravel迁移——在表中添加检查约束

I want to create a table in Laravel Migration like this-

我想在Laravel迁移中创建一个这样的表

CREATE TABLE Payroll
(
 ID int PRIMARY KEY, 
 PositionID INT,
 Salary decimal(9,2) 
 CHECK (Salary < 150000.00)
);

What I have done is-

我所做的是-

Schema::create('Payroll', function (Blueprint $table)
{
    $table->increments('id');
    $table->integer('PositionID ');
    $table->decimal('Salary',9,2);
    //$table->timestamps();
});

But I can't create this-

但我不能创造这个。

 CHECK (Salary < 150000.00)

Can anyone please tell, how to implement this CHECK constraints in Laravel Migration ?

谁能告诉我，在Laravel迁移中如何实现这个检查约束吗?

4 个解决方案

#1

Adding constraints is not supported by the Blueprint class (at least as of Laravel 5.3), however it is possible to add constraints to your tables directly from your migrations, by using database statements.

Blueprint类不支持添加约束(至少Laravel 5.3不支持)，但是可以通过使用数据库语句直接从迁移向表添加约束。

In your migration file,

在你的移民文件,

public function up ()
{
    Schema::create('payroll', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('position_id');
        $table->decimal('salary',9,2);
    });

    // Add the constraint
    DB::statement('ALTER TABLE payroll ADD CONSTRAINT chk_salary_amount CHECK (salary < 150000.00);');
}

#2

I don't think this is a feature in Laravel migrations. I think this is something that will have to be in your Models or Validation logic, unless you add it in manually to MYSQL

我不认为这是Laravel迁移的一个特点。我认为这是你的模型或验证逻辑中必须有的东西，除非你手工添加到MYSQL中

This is what i would do

这就是我要做的

$this->validate($request, [
    'Salary' => 'max:150000.00',
]);

#3

This feature is not included in the Blueprint class, so you can't do that in your migration file.

Blueprint类不包含此特性，所以您不能在迁移文件中这样做。

But in your Payroll model, you can create a mutator:

但是在你的工资单模型中，你可以创建一个换向器:

class Payroll extends Model{

    public function setSalaryAttribute($value){
        $this->attributes['Salary'] = $value < 150000.00 ? $value : 150000.00;
    }

}

So when a payroll Salary attribute is created or updated, this method will be automatically triggered and will check that the new value doesn't exceed 150000.00

因此，当创建或更新一个payroll Salary属性时，该方法将被自动触发，并检查新值不超过150000.00。

EDIT: You should take a look at the mutators documentation in Laravel Docs.

编辑:您应该看看Laravel文档中的mutators文档。

#4

MySQL/Mariadb ignore CHECK constraints, so you have to use triggers instead. Triggers can only be set to run on one of INSERT/UPDATE/DELETE, which means that if we want it to run on both INSERT and UPDATE we have to create a procedure then call it from two separate triggers.

MySQL/Mariadb忽略检查约束，因此必须使用触发器。触发器只能设置为在INSERT/UPDATE/DELETE中运行，这意味着如果我们想让它同时在INSERT和UPDATE中运行，我们必须创建一个过程，然后从两个单独的触发器中调用它。

DB::statement() doesn't support this syntax, so we have to use PDO::exec() instead.

DB::语句()不支持这种语法，所以我们必须使用PDO::exec()。

Here's the TRIGGER syntax for Michael's example:

下面是Michael的例子的触发器语法:

public function up()
{
    Schema::create('Payroll', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('position_id');
        $table->decimal('salary', 9, 2);
    });

    DB::connection()->getPdo()->exec("
        -- Create the procedure
        CREATE PROCEDURE payroll_check_salary_amount (salary INT)
        BEGIN
            IF NOT (salary < 150000.00) THEN
                SIGNAL SQLSTATE '45000' SET message_text = 'salary must be less than 150000.00';
            END IF;
        END;

        -- Create the INSERT trigger
        CREATE TRIGGER payroll_check_salary_amount_insert BEFORE INSERT ON Payroll
        FOR EACH ROW
        BEGIN
            CALL payroll_check_salary_amount(NEW.salary);
        END;

        -- Create the UPDATE trigger
        CREATE TRIGGER payroll_check_salary_amount_update BEFORE UPDATE ON Payroll
        FOR EACH ROW
        BEGIN
            CALL payroll_check_salary_amount(NEW.salary);
        END;
    ");
}

public function down()
{
    Schema::dropIfExists('Payroll');
    DB::statement('DROP PROCEDURE IF EXISTS payroll_check_salary_amount');
}

#1