Set-null-if-no-cycle-check is wrongly placed for MSSQL?

mookeshnanda · 11 October 2023 18:25

Is onDelete case of set-null-if-no-cycle-check wrongly placed to use “on delete no action”

pixl8/Preside-CMS/blob/stable/system/services/database/adapters/MsSqlAdapter.cfc#L131


      
          		sql &= " foreign key ( #escapeEntity( arguments.sourceColumn )# )";
          		sql &= " references #escapeEntity( arguments.foreignTable )# ( #escapeEntity( arguments.foreignColumn )# )";
          
          		switch( arguments.onDelete ) {
          			case 'error':
          				break;
          			case 'cascade':
          				sql &= " on delete cascade";
          				break;
          			case 'cascade-if-no-cycle-check':
          			case 'set-null-if-no-cycle-check':
          			case 'no action':
          				sql &= " on delete no action";
          				break;
          			default:
          				sql &= " on delete set null";
          		}
          		switch( arguments.onUpdate ) {
          			case 'error':
          				break;
          			case 'cascade':

dominic.watson · 12 October 2023 14:41

No, I don’t think so. What makes you ask?

mookeshnanda · 12 October 2023 16:44

Owh, the naming for it is set-null but the returned sql is no-action , instead of set null

dominic.watson · 12 October 2023 23:17

The key is this ^^. MSSQL has a broken idea of not allowing these cascades in situations where they are perfectly fine due to a “cycle check”. It doesn’t even allow setting null. So in this case we can’t do anything about it and just have to let MSSQL break when the record tries to be deleted.

mookeshnanda · 14 October 2023 03:40

I see, that’s good to know