5
Vote

Cannot drop column if the column has a default constraint

description

SQL Server doesn't allow you to drop a column that has a default constraint; rather it demands that the constraint be dropped first. Orchard allows us to create a default constraint using WithDefault, but DropColumn does not take this constraint into account and therefor will fail to drop the column.

To drop the column, first it needs to be known if there is a default constraint, which should be dropped if one is found.

Either DropColumn should do this, or perhaps it'd be better if there were a DropDefault command that can in turn be used by DropColumn.

Example:

SchemaBuilder.AlterTable("RealEstatePartRecord", cfg => cfg.AddColumn<DateTime>("datecolumn1", col => col.WithDefault(DateTime.Now)));

SchemaBuilder.AlterTable("RealEstatePartRecord", cfg => cfg.DropColumn("datecolumn1"));

The second line will throw an SQL exception.

A way to find the name of a default constraint for a given table and column could be something like this:

declare @column nvarchar(128)
declare @table nvarchar(128)

set @table = 'table1'
set @column = 'column_1'

select dc."name" as "DF_constraint"
from sys.default_constraints dc
join sys.columns c on c.default_object_id = dc.object_id
where dc.parent_object_id = OBJECT_ID(@table)
and c."name" = @column

comments

Jetski5822 wrote Jun 7, 2013 at 7:53 PM

will be changing the data access layer for this one :(

CSADNT wrote Oct 30, 2013 at 9:17 PM

The name of the constraint is not predictable and it is a bug from NHibernate which should use the table and to column to name the constraint, not add an autogenerated number to the column name.