📋

Description

Error 1169 indicates an attempt to write data to a MariaDB table that violates a unique constraint. This happens when an `INSERT` or `UPDATE` operation tries to store a duplicate value in a column (or set of columns) that is configured to only allow unique values. MariaDB prevents the write to maintain data integrity, ensuring no two rows have the same value for the specified unique key.

💬

Error Message

Can't write, because of unique constraint, to table '%s'

🔍

Known Causes

4 known causes

⚠️

Duplicate Data Insertion

An `INSERT` statement is attempting to add a new row with a value in a unique column that already exists in the table.

⚠️

Unique Key Update Conflict

An `UPDATE` statement is trying to change a column's value to one that is already present in another row for the same unique key.

⚠️

Application Data Generation Error

The application logic is generating non-unique values for a column intended to be unique, leading to write attempts that violate the constraint.

⚠️

Concurrent Write Race Condition

Multiple clients simultaneously attempt to insert or update the same unique value, causing a conflict when one operation commits before the other.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Records medium

Find and delete the offending duplicate rows that violate the unique constraint.

1

Identify the table and the columns involved in the unique constraint. You can usually find this information in your application's error logs or by querying the `INFORMATION_SCHEMA.STATISTICS` table.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND CONSTRAINT_NAME = 'your_unique_constraint_name';

2

Construct a query to find duplicate rows based on the columns in the unique constraint. Replace `your_table_name` and `column1`, `column2` with your actual table and column names.

SELECT column1, column2, COUNT(*) FROM your_table_name GROUP BY column1, column2 HAVING COUNT(*) > 1;

3

Carefully review the output of the previous query. Decide which of the duplicate records you want to keep and which to delete. It's highly recommended to back up your data before proceeding with deletions.

BACKUP DATABASE your_database_name TO '/path/to/your/backup.sql';

4

Delete the duplicate rows. This can be tricky and depends on how you want to resolve duplicates. One common method is to delete all but one of the duplicates. Ensure you have a way to uniquely identify the rows to delete (e.g., a primary key or a timestamp). Replace `pk_column` with your primary key column and `column1`, `column2` with the columns in the unique constraint.

DELETE FROM your_table_name WHERE pk_column NOT IN (SELECT MIN(pk_column) FROM your_table_name GROUP BY column1, column2);

5

After deleting duplicates, attempt the operation that previously failed.

2. Adjust Application Logic to Prevent Duplicates medium

Modify your application code to ensure unique values are not inserted or updated.

1

Review the code that is performing the INSERT or UPDATE operation that is failing. Identify where the data is coming from and how it's being generated.

2

Implement checks within your application logic before attempting to write to the database. This could involve querying the table to see if a record with the same unique key already exists.

SELECT COUNT(*) FROM your_table_name WHERE column1 = 'value1' AND column2 = 'value2';

3

If a duplicate is found, either prevent the insert/update, or provide feedback to the user. If you're using an ORM (Object-Relational Mapper), check its documentation for ways to handle unique constraint violations gracefully.

4

Consider using `INSERT IGNORE` or `INSERT ... ON DUPLICATE KEY UPDATE` statements in your SQL if your application logic allows for such behavior (e.g., if you want to ignore duplicates or update existing records).

INSERT IGNORE INTO your_table_name (column1, column2, ...) VALUES ('value1', 'value2', ...);

-- Or for updating:
INSERT INTO your_table_name (column1, column2, ...) VALUES ('value1', 'value2', ...) ON DUPLICATE KEY UPDATE column3 = 'new_value';

3. Temporarily Disable and Re-enable Unique Constraint easy

A quick workaround for specific scenarios, allowing an operation to complete and then re-enforcing uniqueness.

1

Identify the name of the unique constraint. This can be found using `SHOW INDEX FROM your_table_name;` and looking for `Non_unique` = 0.

SHOW INDEX FROM your_table_name;

2

Disable the unique constraint. Replace `your_table_name` and `your_unique_constraint_name`.

ALTER TABLE your_table_name DROP INDEX your_unique_constraint_name;

3

Perform the operation that was previously failing. This should now succeed.

4

Re-create the unique constraint. This will fail if duplicates still exist, forcing you to address them. You might need to re-run 'Identify and Remove Duplicate Records' if this step fails.

ALTER TABLE your_table_name ADD UNIQUE INDEX your_unique_constraint_name (column1, column2);

🔗

Related Errors

5 related errors

MariaDB Error 1169: Unique Constraint Violation