Laravel Migration - Adding Check Constraints In Ta

问题:

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

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 ?

回答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.

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

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.

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

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

回答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.

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

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

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');
}

回答5:

To Add constraints you can make the following code

$table->enum('choices', ['foo', 'bar']);

but if you want to make it with arithmetic operation the solution is to make it with sql statement like mentioned by**@Michael**

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

check the following link for more information

回答6:

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

    // Add the constraint`enter code here`
    DB::statement('ALTER TABLE payroll ADD CONSTRAINT check_salary_amount CHECK (payment < 2000.00);');
}