U4-7760 - Attempt to delete a document type that acts as a composition of another generates error message

Created by Andy Butland 17 Jan 2016, 16:18:13 Updated by Stephan 03 Feb 2016, 16:51:12

Error message is:

ERROR Umbraco.Core.Persistence.UmbracoDatabase - Database exception occurred
System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_cmsPropertyData_cmsPropertyType_id". The conflict occurred in database "Umbraco74", table "dbo.cmsPropertyData", column 'propertytypeid'.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0()
   at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args)
Error Number:547,State:0,Class:16

Would say it's right that you shouldn't be able to delete types used in compositions, but would be better if the messaging was more clear as to why, or, perhaps, you didn't have the option to delete in the first place - like in the case of folders with something inside them.


Andy Butland 19 Jan 2016, 07:17:55

To clarify a little, the foreign key constraint being violated here is for the relation between content and document types, not between the document types that make up the composition. The simple test case is as follow:

  • Create Doc Type A (with at least one field)
  • Create Doc Type B composed of Doc Type A
  • Create an instance of content of Doc Type B
  • Delete Doc Type A --> the error message above
  • If you were to delete Doc Type B, this works as expected - the content would be deleted and there's no error.

Can see it's been considered as there's a comment //NOTE What about content that has the contenttype as part of its composition? in ContentService.DeleteContentOfType().

Seems like either:

  • All content that is of a document type that composes the document type being deleted, should be deleted
  • Or you don't allow the deletion of document types that are used in compositions until all the types that compose them are deleted (by which point we know all content that indirectly uses the document type used in a composition has been removed).

The latter sounds safer - potentially otherwise your whole content tree could go with one delete operation, which would be dangerous and for large sites, an expensive operation.

Andy Butland 19 Jan 2016, 14:23:15

PR for the above here: https://github.com/umbraco/Umbraco-CMS/pull/1033

Stephan 03 Feb 2016, 15:24:12

Could not repro with 7.4 as of today. Deleting DocTypeA gets rid of it, but not DocTypeB, only of course DocTypeA (and its property) is gone from the composition. Can edit the content, the property is gone, but everything seems OK.

@abutland Any chance you can test latest 7.4 code and confirm?

Andy Butland 03 Feb 2016, 16:44:26

Hi @zpqrtbnk - yes, I just have, and it works as you describe. Not seeing the exception any more, so all looks fine. Have closed the PR.

Priority: Normal

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted: Pull request

Affected versions: 7.4.0

Due in version: 7.4.0

Sprint: Sprint 8

Story Points: