
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
- Remove array keys and values if it does not exist in other array in Laravel
- If no route matched route::fallback in laravel
- Use withCount() to Calculate Child Relationship Records
- Class 'App\Rules\Hash' not found in Laravel
- How to access the nth object from Laravel collection object ?
- Cannot end a section without first starting one
- Laravel append URI in route
- Non-static method App\Http\Helper::myFunction() should not be called statically
- How to print form data in laravel
- Display success message in laravel
- Run artisan command to generate key in laravel
- How to validate form input data in laravel
- Display option of select as selected with blade directive Laravel
- Call to a member function pluck() on array
- Send id with route Laravel
- Comment .env file in laravel
- How to check find method executed successfully in laravel
- Where to use whereNotNull eloquent in laravel
- How to call Laravel route in jQuery
- Get Array of IDs from Eloquent Collection
- Attempt to read property "avatar" on null in Laravel
- How to update record after save method in Laravel
- On delete set foreign id column value null using migration in laravel 8
- Conditional validation in laravel
- How to get route method name in Laravel