
SQLSTATE[23000]: Integrity constraint violation: 1022 Can't write; duplicate key in table
The database already has a constraint with this particular name. Constraint names must not be duplicated for the entire database. You can find Constraints names under information_schema »View: KEY_COLUMN_USAGE in CONSTRAINT_NAME column. You can change constraint name to avoid SQLSTATE[23000]: Integrity constraint violation: 1022 Can't write; duplicate key in table Error.
-
Pivot table migration with custom constraint name
--PATH database\migrations\<yourmigrationfile>.php//Migration command to create migration file php artisan make:migration create_role_user_table --create=role_user //Migration file code under - database\migrations\<yourmigrationfile>.php public function up() { Schema::create('role_user', function (Blueprint $table) { $table->foreignId('role_id')->constrained(); $table->foreignId('user_id')->constrained(); $table->foreign('user_id', 'fk_user_id')->references('id')->on('users')->onDelete('cascade'); $table->foreign('role_id', 'fk_role_id')->references('id')->on('roles')->onDelete('cascade'); $table->primary(['user_id', 'role_id']); $table->timestamps(); }); }
This code snippet is helpful if you are getting
SQLSTATE[23000]: Integrity constraint violation: 1022 Can't write; duplicate key in table
Error while migration. You can create a table with your own custom constraint keyname. Laravel uses the default constraint naming convention to create a constraint name if you did not pass the value to $table->foreign() method. Here we pass (fk_user_id) as constraint name as own custom constraint name.
You can also run SQL query to get the table schema and table name if their constraint exists. Just replace the value instead of 'role_user_role_id_foreign', 'role_user_user_id_foreign' as per your constraint name.SELECT `TABLE_SCHEMA`, `TABLE_NAME` FROM `information_schema`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_NAME` IN ('role_user_role_id_foreign', 'role_user_user_id_foreign')
. -
Add constraint in existing table in MySQL
//Add constraint in an existing table ALTER TABLE role_user ADD CONSTRAINT fk_role_id FOREIGN KEY (role_id) REFERENCES roles (id); //If you are getting an error Can't write; duplicate key in table '#sql-15fc_475' and want to use the same constraint name which already exists then first drop the existing foreign key and index, after that run the above query to create the constraint name. // Drop constraint in MySQL ALTER TABLE role_user DROP FOREIGN KEY fk_role_id; ALTER TABLE role_user DROP INDEX fk_role_id;
This code snippet adds the constraint fk_role_id on the role_user table with references on the roles table’s id. If you are getting the error
Can't write; duplicate key in table '#sql-15fc_475'
while executing MySQL query that means constraint with name fk_role_id already exists then you have to change the constraint name instead of ‘fk_role_id’, use ‘fknew_role_id’ or as per your requirement.If you want to use the same constraint name which already exists then first drop the existing constraint and run add constraint SQL query to create constraint. There is no SQL query to drop constraint in MySQL. You have to drop foreign key and index and after that, you can add a constraint to create a constraint with this specific name.
If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.
Don't forget to share this article! Help us spread the word by clicking the share button below.
We appreciate your support and are committed to providing you valuable and informative content.
We are thankful for your never ending support.
Random Code Snippet Queries: Laravel
- Run artisan command to generate key in laravel
- Laravel route redirect not working
- Use withCount() to Calculate Child Relationship Records
- SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.post_id' in 'where clause
- How to delete record in Laravel with ajax
- SQLSTATE[42000]: Syntax error or access violation: 1055
- After image selected get validation error in laravel
- Array to string conversion laravel blade
- Laravel specific table Migration
- Laravel recursive function in controller
- Ignore Records where a field has NULL value in Laravel
- Laravel csrf token mismatch for ajax POST Request
- Update if exist else insert new record in laravel
- How to create projects method with belongstomany relationship in user model
- How to get all posts which contains comments in laravel
- Laravel pagination links with query string
- How to create controller in laravel
- How to add a key value pair to existing array in laravel
- Send OTP using textlocal api in laravel
- Print last executed query in laravel
- Get only 10 records from table in laravel
- How to get random string in Laravel
- Database transactions in laravel
- How to add class to tr in table using foreach in laravel
- How to display order by null last in laravel