A default constraint in a table defines a value for a column where no value is specified. You can name a default constraint as in the example given:
-
create table dbo.mytable ( id integer , myvalue integer constraint myconstraintname default 77 )
We created a table with a constraint on the column myvalue and the constraint is named myconstraintname. Now you can add a row to that table without giving a value for the column myvalue:
-
insert into dbo.mytable (id) values (1)
The table has the following row:
-
select * from dbo.mytable
When the constraint is no longer needed, it is quite easy to delete the constraint as you can use the constraint name.
-
alter table dbo.mytable drop constraint myconstraintname
Let’s consider another table with the same structure. But this time we do not name the constraint.
-
create table dbo.mytable2 ( id integer , myvalue integer default 77 ) Here it is also possible to add a row without giving a value for the columnname myvalue: insert into dbo.mytable2 (id) values (1)
We can see that the table has one row:
-
select * from dbo.mytable2
Now we want to delete the default constraint without creating a new table or without deleting the column myvalue. As we do not have a constraint name we cannot use the same statement as for the first table. We did not name our constraint. But nevertheless the constraint is stored in the system tables of sql server. Sql server gave the constraint an internal name that we can use. The following sql statement shows where we can find the internal name of our constraint:
-
select t4.name , t4.object_id , t4.schema_id , t4.parent_object_id , t4.type , t4.type_desc , t2.name as schemaname , t1.name as tablename , t3.name as columnname from [sys].[tables] t1 join [sys].[schemas] t2 on t1.schema_id = t2.schema_id join [sys].[all_columns] t3 on t1.object_id = t3.object_id join [sys].[default_constraints] t4 on t1.object_id = t4.parent_object_id and t3.column_id = t4.parent_column_id where t1.name = N’mytable2′ — table name and t2.name = N’dbo‘ — schema name and t3.name = N’myvalue‘ — column name
The internal default constraint name is called „DF__mytable2__myvalu__6FB560CC“. Now you can construct the alter table statement using the system tables of sql server:
-
declare @cmd nvarchar(150) select @cmd = ‚ALTER TABLE ‚ + t2.name + ‚.‘ + t1.name + ‚ DROP CONSTRAINT ‚ + t4.name from &SQUARE_BRACKETS_OPEN;sys].&SQUARE_BRACKETS_OPEN;tables] t1 join &SQUARE_BRACKETS_OPEN;sys].&SQUARE_BRACKETS_OPEN;schemas] t2 on t1.schema_id = t2.schema_id join &SQUARE_BRACKETS_OPEN;sys].&SQUARE_BRACKETS_OPEN;all_columns] t3 on t1.object_id = t3.object_id join &SQUARE_BRACKETS_OPEN;sys].&SQUARE_BRACKETS_OPEN;default_constraints] t4 on t1.object_id = t4.parent_object_id and t3.column_id = t4.parent_column_id where t1.name = N’mytable2′ — table name and t2.name = N’dbo‘ — schema name and t3.name = N’myvalue‘ — column name exec(@cmd)
The script generates the following statement:
-
ALTER TABLE dbo.mytable2 DROP CONSTRAINT DF__mytable2__myvalu__6FB560CC
The statement is stored in the variable @cmd and finally the content of the variable @cmd is executed deleting the default constraint.
Kommentare (0)