When soft deleting an entry which contains a column with a 'unique' constraint, the value used in this column is no longer usable or visible to the user. Trying to reuse the value of the deleted entry leads to UniqueConstraintViolationException being thrown.
Steps to reproduce
0) Have a table with columns id(as primary key), name(which has unique constraint)
1) Create an entry with following values (1, 'John')
2) Delete the entry
3) Create an entry with following values (2, 'John')
4) A UniqueConstraintViolationException is thrown
Expected behavior
The entry with values (2, 'John') should get successfully created (the values of deleted entries should be reusable)
Logs/Trace
(3/3) UniqueConstraintViolationExceptionAn exception occurred while executing 'INSERT INTO package_key (created_at, updated_at, version, name, size, enabled) VALUES(UTC_TIMESTAMP(), UTC_TIMESTAMP(), 0, ?, ?, ?)' with params ["beta_package_10000", 123, 0]:SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'beta_package_10000' for key 'UK_2hlyv82hvqpcjrc2ygbe76ypk'
in AbstractMySQLDriver.php line 66
at AbstractMySQLDriver->convertException('An exception occurred while executing \'INSERT INTO package_key (created_at, updated_at, version, name, size, enabled) VALUES(UTC_TIMESTAMP(), UTC_TIMESTAMP(), 0, ?, ?, ?)\' with params ["beta_package_10000", 123, 0]:SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry \'beta_package_10000\' for key \'UK_2hlyv82hvqpcjrc2ygbe76ypk\'', object(PDOException))in DBALException.php line 128
at DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'INSERT INTO package_key (created_at, updated_at, version, name, size, enabled) VALUES(UTC_TIMESTAMP(), UTC_TIMESTAMP(), 0, ?, ?, ?)', array('beta_package_10000', 123, 0))in Connection.php line 1015
at Connection->executeUpdate('INSERT INTO package_key (created_at, updated_at, version, name, size, enabled) VALUES(UTC_TIMESTAMP(), UTC_TIMESTAMP(), 0, ?, ?, ?)', array('beta_package_10000', 123, 0), array())in QueryBuilder.php line 208
at QueryBuilder->execute()in MySQLData.php line 473
at MySQLData->create(object(Entity))in ControllerProvider.php line 143
at ControllerProvider->modifyEntity(object(Application), object(MySQLData), object(Entity), 'package_key', false)in ControllerProvider.php line 331
at ControllerProvider->create(object(Application), 'package_key')in HttpKernel.php line 151
at HttpKernel->handleRaw(object(Request), 1)in HttpKernel.php line 68
at HttpKernel->handle(object(Request), 1, true)in Application.php line 496
at Application->handle(object(Request))in Application.php line 477
at Application->run()in index.php line 899
If CRUDlex' soft delete feature is used, there should not be an actual unique constraint on that CRUDlex-unique-field. This is just the nature of this thing. Two possible solutions here:
Don't use soft delete for this table (likely not recommended)
Issue Description
When soft deleting an entry which contains a column with a 'unique' constraint, the value used in this column is no longer usable or visible to the user. Trying to reuse the value of the deleted entry leads to UniqueConstraintViolationException being thrown.
Steps to reproduce
0) Have a table with columns id(as primary key), name(which has unique constraint) 1) Create an entry with following values (1, 'John') 2) Delete the entry 3) Create an entry with following values (2, 'John') 4) A UniqueConstraintViolationException is thrown
Expected behavior
The entry with values (2, 'John') should get successfully created (the values of deleted entries should be reusable)
Logs/Trace
(3/3) UniqueConstraintViolationExceptionAn exception occurred while executing 'INSERT INTO
package_key
(created_at, updated_at, version,name
,size
,enabled
) VALUES(UTC_TIMESTAMP(), UTC_TIMESTAMP(), 0, ?, ?, ?)' with params ["beta_package_10000", 123, 0]:SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'beta_package_10000' for key 'UK_2hlyv82hvqpcjrc2ygbe76ypk'in AbstractMySQLDriver.php line 66 at AbstractMySQLDriver->convertException('An exception occurred while executing \'INSERT INTO
package_key
(created_at, updated_at, version,name
,size
,enabled
) VALUES(UTC_TIMESTAMP(), UTC_TIMESTAMP(), 0, ?, ?, ?)\' with params ["beta_package_10000", 123, 0]:SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry \'beta_package_10000\' for key \'UK_2hlyv82hvqpcjrc2ygbe76ypk\'', object(PDOException))in DBALException.php line 128 at DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'INSERT INTOpackage_key
(created_at, updated_at, version,name
,size
,enabled
) VALUES(UTC_TIMESTAMP(), UTC_TIMESTAMP(), 0, ?, ?, ?)', array('beta_package_10000', 123, 0))in Connection.php line 1015 at Connection->executeUpdate('INSERT INTOpackage_key
(created_at, updated_at, version,name
,size
,enabled
) VALUES(UTC_TIMESTAMP(), UTC_TIMESTAMP(), 0, ?, ?, ?)', array('beta_package_10000', 123, 0), array())in QueryBuilder.php line 208 at QueryBuilder->execute()in MySQLData.php line 473 at MySQLData->create(object(Entity))in ControllerProvider.php line 143 at ControllerProvider->modifyEntity(object(Application), object(MySQLData), object(Entity), 'package_key', false)in ControllerProvider.php line 331 at ControllerProvider->create(object(Application), 'package_key')in HttpKernel.php line 151 at HttpKernel->handleRaw(object(Request), 1)in HttpKernel.php line 68 at HttpKernel->handle(object(Request), 1, true)in Application.php line 496 at Application->handle(object(Request))in Application.php line 477 at Application->run()in index.php line 899