Laravel migration - adding control constraints to a table

Question

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

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

What i did is-

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

But I can not create this-

 CHECK (Salary < 150000.00)

Can someone tell how to implement restrictions CHECKin Laravel Migration ?

Answer 1

Adding constraints is not supported by the Blueprint class (at least since Laravel 5.3), however, you can add constraints to your tables directly from your migrations using database operators.

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

Answer 2

, Laravel. , , , MYSQL

,

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

Answer 3

Blueprint, .

Payroll :

class Payroll extends Model{

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

}

, , , 150000.00

EDIT: Laravel Docs.

Answer 4

MySQL/Mariadb CHECK, . INSERT/UPDATE/DELETE, , , INSERT, UPDATE, , .

DB:: statement() , PDO:: exec().

TRIGGER :

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

Answer 5

,

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

, , SQL, ** @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

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