Laravel 迁移 - 在表中添加检查约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38223158/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel Migration - Adding Check Constraints In Table
提问by Abrar Jahin
I want to create a table in Laravel Migration like this-
我想像这样在 Laravel Migration 中创建一个表 -
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 CHECKconstraints in Laravel Migration?
谁能告诉，如何CHECK在Laravel Migration 中实现这个约束？
回答by Michael
Adding constraints is not supported by the Blueprint class (at least as of Laravel 5.3), however it ispossible to add constraints to your tables directly from your migrations, by using database statements.
添加约束不是由蓝图类（至少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);');
}
回答by Ben Clayton
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',
]);
回答by Gerard Reches
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:
但是在您的薪资模型中，您可以创建一个 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
因此，当创建或更新工资单 Salary 属性时，将自动触发此方法并检查新值是否不超过 150000.00
EDIT: You should take a look at the mutators documentationin Laravel Docs.
编辑：您应该查看Laravel Docs中的mutators文档。
回答by Kael Watts-Deuchar
MySQL/Mariadb ignore CHECKconstraints, 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 忽略CHECK约束，因此您必须改用触发器。触发器只能设置为在 INSERT/UPDATE/DELETE 之一上运行，这意味着如果我们希望它在 INSERT 和 UPDATE 上运行，我们必须创建一个过程，然后从两个单独的触发器调用它。
DB::statement() doesn't support this syntax, so we have to use PDO::exec() instead.
DB::statement() 不支持这种语法，所以我们必须使用 PDO::exec() 来代替。
Here's the TRIGGERsyntax for Michael's example:
以下是TRIGGERMichael 示例的语法：
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');
}
回答by Amirouche Zeggagh
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**
但是如果你想用算术运算来实现它，解决方案是用 **@Michael** 提到的 sql 语句来实现它
// Add the constraint
DB::statement('ALTER TABLE payroll ADD CONSTRAINT chk_salary_amount CHECK (salary < 150000.00);');
check the following linkfor more information
查看以下链接以获取更多信息
回答by Muhammad Adil Muneer
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);');
}
