Laravel Migration - Adding Check Constraints In Table
Solution 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);');
}
Solution 2
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.
Solution 3
This answer is outdated, MariaDB 10.2.1 and MySQL 8.0.16 both support proper check constraints.
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');
}
Related videos on Youtube
Abrar Jahin
I am Software Engineer at Relisource Inc. Working on ASP.Net Core, Android, Bootstrap, CSS3, jQuery, Angular2. Some of My other profiles are- Github Uva OnlineJudge LeetCode LinkedIn FaceBook
Updated on September 15, 2022Comments
-
Abrar Jahin over 1 year
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 ?-
Pascal Bakhuis over 5 yearsFor the Googlers among us Kael Watts-Deuchar's answer is in need of an update. As of MariaDB 10.2.1 MariaDB now supports check constraints. (I can't directly comment on Kael as I lack the 50 required reputation).
-
-
Philipp Schemel almost 8 yearsThis is also the way i would solve this. Use this function in your Controller, where you get the request, right before you save or update the data.
-
Abrar Jahin almost 8 yearsI am not asking it, I am asking for autometic validation with MySQL end, so it is not helping me. But thanks for trying to help :)
-
Michael over 7 yearsYou can add constraints in your migration files, just not using the Blueprint class. See my answer for details.
-
AngryUbuntuNerd almost 6 yearsThis approach works but please note that it won't work with SQLite (for example when used as in-memory database for testing). SQLite does not support altering constraints after table creation. To work around that, you need to make sure to only add the constraint on anything but SQLite.
-
Amir Shabani almost 5 yearsPlease edit your answer and explain why your code works!
-
Naveed Ali over 2 yearsPrior to MySQL 8.0.16, CREATE TABLE permits only the following limited version of table CHECK constraint syntax, which is parsed and ignored: As of MySQL 8.0.16, CREATE TABLE permits the core features of table and column CHECK constraints, for all storage engines. CREATE TABLE permits the following CHECK constraint syntax, for both table constraints and column constraints: dev.mysql.com/doc/refman/8.0/en/…
-
Peter Chaula over 2 yearsThis will silently cap the amount which is the complete opposite of what the CHECK CONSTRAINT would do - throw an error. I suggest you throw an error if you want to somewhat mimick the behaviour of the CHECK CONSTRAINT