SoftDelete sets "default" => null for deleted_at field, causing MSSQL to silently create a Default Constraint which in turns causes Migrations to fail when reverting

When MSSQL receives even just a default value of null it still creates a default constraint. Said constraint prevents doctrine from removing the deleted_at field in migrations (due to the dependency on the constraint).

Removing "default" => null prevents the silent creation of the constraint.

I have no machine to test the effects of this on MySQL. I would imagine that it would not materially affect MySQL as the default values are never used anyways (hence the default value of null).

Attached is a patch to fix the behavior (for rev 7544 in /branches/1.2)

—

If one is running into problems with migrations being unable to move backwards due to default constraints on SoftDelete columns, one can run the following T-SQL script to remove all default value constraints from a database (EVEN THOSE YOU SET MANUALLY, USE WITH CAUTION):

{{
– This script removes ALL default constraints

USE YOURDATABASENAMEHERE;

Declare @name nvarchar(155)
Declare @table nvarchar(155)
Declare @sql nvarchar(1000)

– find constraint names
DECLARE default_constraints CURSOR FOR
SELECT
object.name,
parent.name
FROM
sys.objects AS object
LEFT JOIN sys.objects AS parent ON object.parent_object_id = parent.object_id
WHERE
object.type_desc LIKE '%CONSTRAINT'
AND object.type_desc LIKE 'DEFAULT_CONSTRAINT'

OPEN default_constraints

FETCH NEXT FROM default_constraints INTO @name, @table

WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT @name IS NULL
BEGIN
SELECT @sql = 'ALTER TABLE [' + @table + '] DROP CONSTRAINT [' + @name + '];'
--PRINT @sql
EXECUTE sp_executesql @sql
END

FETCH NEXT FROM default_constraints INTO @name, @table
END
CLOSE default_constraints
DEALLOCATE default_constraints
}}