In SQL Server, Constraints are rules that can be added to the columns of a table.
There are several types of constraint:
In SQL Server, when you create a constraint you can specify a name. If you do not specify a name, SQL Server will allocate its own random name. This is very nice, but because the names are random, they vary from one database to another. This causes problems for maintenance script writers who may need to drop the constraint to make a change to the underlying column.
This T-SQL fragment creates an unnamed DEFAULT constraint on the MyColumn column of the MyTable table.
The first section of the script below uses SQL's own system tables to interrogate the database schema. It finds the constraint using the table name and a wildcard 'like' search using the piece of the constraint name that SQL Server will always generate. You will need to modify this part of the query with your table name and partial constraint name. The next step is to builds a dynamic SQL statement to drop the constraint using its actual name. The statement is then executed using the system stored procedure: sp_executesql
There are several types of constraint:
| Primary Key: | Identifies the table's primary key column(s) |
| Unique: | Makes sure that values are not duplicated in the column |
| Foreign Key: | Used to enforce referential integrity |
| Check: | Places additional logic checks on a column, for example, cannot be zero |
| Default: | Provides a default value used when a value is not provided for the column in an INSERT statement |
In SQL Server, when you create a constraint you can specify a name. If you do not specify a name, SQL Server will allocate its own random name. This is very nice, but because the names are random, they vary from one database to another. This causes problems for maintenance script writers who may need to drop the constraint to make a change to the underlying column.
This T-SQL fragment creates an unnamed DEFAULT constraint on the MyColumn column of the MyTable table.
ALTER TABLE [MyTable] ADD CONSTRAINT DEFAULT (0) FOR [MyColumn]It should be considered as good practice to always specify constraints with a name, like below.
ALTER TABLE [MyTable] ADD CONSTRAINT [CONSTRAINT_NAME] DEFAULT (0) FOR [MyColumn]If you do not specify a name, SQL will generate a random name built from a prefix which denotes the type of constraint, and partial table and column names followed by a random alphanumeric string. It would normally look something like this:
DF__MyTable__MyColu__3F3159AB
If you are unfortunate enough to have to deal with a database that contains unnamed constraints and you need to drop them then you'll need to find the name of the constraint that you want to drop.The first section of the script below uses SQL's own system tables to interrogate the database schema. It finds the constraint using the table name and a wildcard 'like' search using the piece of the constraint name that SQL Server will always generate. You will need to modify this part of the query with your table name and partial constraint name. The next step is to builds a dynamic SQL statement to drop the constraint using its actual name. The statement is then executed using the system stored procedure: sp_executesql
declare @name nvarchar(32), @sql nvarchar(1000) -- find constraint name select @name = O.name from sysobjects AS O left join sysobjects AS T on O.parent_obj = T.id where isnull(objectproperty(O.id,'IsMSShipped'),1) = 0 and O.name not like '%dtproper%' and O.name not like 'dt[_]%' and T.name = 'MyTable' and O.name like 'DF__MyTable__MyColu%' -- delete if found if not @name is null begin select @sql = 'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @name + ']' execute sp_executesql @sql end -- do your ALTER TABLE here -- replace the constraint select @sql = 'ALTER TABLE [MyTable] ADD CONSTRAINT [' + @name + '] DEFAULT (0) FOR [MyColumn]' execute sp_executesql @sqlHope this helps :-)
365 comments,
Database, Thursday, November 24, 2005 10:31
