php Laravel SQLSTATE[23000]：违反完整性约束：1062 重复条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24948524/
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 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry
提问by Tanveer
i am very new in Laravel and this is my first project in Laravel.As usual, first of all i am developing a full user authentication system.I can registered an single user,can send an user verification email and after clicking that link i can activate a new user account, can login and can logout.But after that whenever i am trying to registered another new user and after clicking the verification link, i am facing an exception which is,
我是 Laravel 的新手，这是我在 Laravel 的第一个项目。像往常一样，首先我正在开发一个完整的用户身份验证系统。我可以注册一个用户，可以发送用户验证电子邮件，点击该链接后我可以激活一个新用户帐户，可以登录并可以注销。但是之后，每当我尝试注册另一个新用户并单击验证链接后，我都会遇到一个异常，即，
Illuminate \ Database \ QueryException
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'users_code_unique' (SQL: update `users` set `code` = , `active` = 1, `updated_at` = 2014-07- 25 04:26:06 where `id` = 41)
now this is my route.php,
现在这是我的 route.php，
<?php
Route::get('/',array(
'as' =>'home',
'uses' =>'HomeController@index'
));
Route::get('/signin',array(
'as' =>'signin',
'uses' =>'AccountController@signinGet'
));
Route::get('/signup',array(
'as' => 'signup',
'uses' => 'AccountController@signupGet'
));
/*
/*
/Authenticated Group
*/
Route::group(array('before' => 'auth'),function(){
/*
/Sign Out(GET)
*/
Route::get('/signout',array
(
'as' => 'signout',
'uses' => 'AccountController@signoutGet'
));
});
/*
/UnAuthenticated Group
*/
Route::group(array('before' => 'guest'),function(){
/* CSRF Protect*/
Route::group(array('before' => 'csrf'),function(){
/*
/ Create Account(POST)
*/
Route::post('/signup',array(
'as'=> 'signup',
'uses'=>'AccountController@signupPost'
));
/*
/ Sign In(POST)
*/
Route::post('/signin',array(
'as' => 'signin-post',
'uses' => 'AccountController@signinPost'
));
});
/*
/ Sign In (GET)
*/
Route::get('/signin',array(
'as' => 'signin',
'uses' => 'AccountController@signinGet'
));
/*
/Create Account(GET)
*/
Route::get('/signup',array(
'as' => 'signup',
'uses'=> 'AccountController@signupGet'
));
Route::get('signup/account/activate/{code}',array(
'as' =>'activate-account',
'uses' =>'AccountController@activatePost'
));
});
?>
and this is my AccountController
这是我的 AccountController
<?php
class AccountController extends \BaseController {
public function signinGet()
{
return View::make('account.signin');
}
public function signinPost(){
$validator = Validator::make(Input::all(),array(
'email' => 'required|email',
'password' => 'required'
));
if($validator->fails()){
//redirect to the signin page
return Redirect::route('signin')
->withErrors($validator)
->withInput();
}else{
//Attempt user singin
$auth = Auth::attempt(array
(
'email' => Input::get('email'),
'password' => Input::get('password'),
'active' => 1
));
if($auth){
//Redirect To intented URL
return Redirect::intended('/');
}
else
{
return Redirect::route('signin')
->with('global','The username or password you provided is wrong or account not activated!');
}
}
return Redirect::route('signin')
->with('global','There is a problem Signing You in.');
}
/**
* Show the form for creating a new resource.
*
* @return Response
*/
public function signupGet()
{
return View::make('account.signup');
}
public function signupPost()
{
$validator = Validator::make(Input::all(), array(
'email' => 'required|max:255|email|unique:users',
'username' => 'required|min:3|unique:users',
'password' => 'required|min:6',
'password_again' => 'required|same:password'
)
);
if($validator->fails())
{
return Redirect::route('signup')
->withErrors($validator)
->withInput();
}else
{
$email = Input::get('email');
$username = Input::get('username');
$password = Input::get('password');
//Activation Code
$code = str_random(60);
$user = User::create(array(
'email' => $email,
'username' => $username,
'password' => Hash::make($password),
'code' => $code,
'active' => 0
)
);
if($user){
//User Activation Code Creation
Mail::send('emails.auth.activate', array('link' => URL::route('activate-account',$code), 'username' => $username),function($message) use ($user)
{
$message->to($user->email,$user->username)->subject('Activate Your Account');
});
return Redirect::route('signup')
->with('global','Your Account has been created! We have sent you an email to activate your account.Please Check the both the Inbox and Spam Folder.');
}
}
//return 'This is a Post Result';
}
public function activatePost($code){
$user = User::where('code','=',$code)->where('active','=',0);
if($user->count()){
$user = $user->first();
$user->active = 1;
$user->code = '';
if($user->save()){
return Redirect::route('home')
->with('global','Activated!.You can sign in now!');
}
}
else{
return Redirect::route('signup')
->with('global','Sorry!We could not activate your acount,please try again later.');
}
}
public function signoutGet(){
Auth::logout();
return Redirect::route('home');
}
}
?>
and this is my create user migration file
这是我创建的用户迁移文件
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateUsersTable extends Migration {
public function up()
{
Schema::create('users', function(Blueprint $table)
{
$table->increments('id');
$table->string('username',255)->unique();
$table->string('email',255)->unique();
$table->string('password',60);
$table->string('password_temp',60);
$table->string('code',60)->unique();
$table->integer('active');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('users');
}
}
?>
and this is my user.php
这是我的 user.php
<?php
use Illuminate\Auth\UserTrait;
use Illuminate\Auth\UserInterface;
use Illuminate\Auth\Reminders\RemindableTrait;
use Illuminate\Auth\Reminders\RemindableInterface;
class User extends Eloquent implements UserInterface, RemindableInterface {
public function getRememberToken()
{
return $this->remember_token;
}
public function setRememberToken($value)
{
$this->remember_token = $value;
}
public function getRememberTokenName()
{
return 'remember_token';
}
protected $fillable = array('email','username','password','password_temp','code','active');
use UserTrait, RemindableTrait;
/**
* The database table used by the model.
*
* @var string
*/
protected $table = 'users';
/**
* The attributes excluded from the model's JSON form.
*
* @var array
*/
protected $hidden = array('password', 'remember_token');
}
?>
now whats the problem?
现在有什么问题吗？
回答by Jarek Tkaczyk
Make sure your codefield is nullable, then instead of setting it's value to empty string, make it null:
确保您的code字段是nullable，然后不要将其值设置为空字符串，而是将其设置为空：
$code = null;
Then you will be able to save it as NULL (MySQL) while it remains unique.
然后您将能够将其保存为 NULL (MySQL)，同时它仍然是唯一的。
Also change this one:
也改变这一点：
$user = User::where('code','=',$code)->where('active','=',0);
if($user->count()){
$user = $user->first();
To:
到：
$user = User::where('code','=',$code)->where('active','=',0)->first();
if(count($user)){
You don't need to call db twice, just check if returned result is not null (countwill do), meaning it returned a Userobject.
您不需要调用 db 两次，只需检查返回的结果是否不为空（count可以），这意味着它返回了一个User对象。
回答by user1927627
your problem can use validator to check. simply use as this:
您的问题可以使用验证器来检查。简单地使用：
use Validator;
use Request;
//...
//unique will pre check the key code weather if unique in tbl_name
public function yourfunc(Request $request) {
// set the rules to check
$rules = ['code'=>'required|unique:tbl_name'];
$validator = Validator::make($request->all(), $rules);
if ($validator->fails()) {
// handler errors
$erros = $validator->errors();
}
//... everything is ok here
}
you can explore more at laravel validation
你可以在Laravel 验证中探索更多
回答by Tom
I found it. You've set code column as unique, though you're setting it to empty string after user click an activation link. And there already is a row in table with code=''; so it throws an error. The problem is here (activatePost):
我找到了。您已将代码列设置为唯一，但在用户单击激活链接后将其设置为空字符串。并且表中已经有一行代码为''; 所以它会抛出一个错误。问题在这里（activatePost）：
$user->code = '';
So either don't empty it, set it to something else or set db colums as not unique.
因此，要么不要清空它，将其设置为其他内容或将 db colums 设置为不唯一。
I would leave the code without emptying it and additionally I would check if user was activated - a simple ifin activatePost. Maybe it's a good idea to verify user not only according to code, but also with a hashed id in link.
我会留下代码而不清空它，另外我会检查用户是否被激活 - activatePost 中的一个简单操作if。也许不仅根据代码验证用户，而且还使用链接中的散列 ID 验证用户是个好主意。
回答by blakroku
There a few things you need to do to improve your code. But duplicate entry usually happens when you set a column to unique and tries to re inset the same data into another row. The most time it get confusing is when you check your table and and find the column empty. Whoop! When a column is set to unique and empty, it means no other column can contain empty data.
您需要做一些事情来改进您的代码。但是当您将一列设置为唯一并尝试将相同的数据重新插入另一行时，通常会发生重复条目。最令人困惑的时候是当您检查表格并发现该列为空时。哎呀！当一列设置为唯一且为空时，意味着没有其他列可以包含空数据。
In simple terms the form of the column can not be duplicated, either null or with data.
简单来说，列的形式是不可复制的，要么是空的，要么是带数据的。
