SQL Server Modify the default constraint

Modify the default constraint using the SSMS database management tool

1, connect the database, select Data table-Right click-"Select Design."

2, in the Table Designer window-"Select the data column to modify-" in the column properties found in the default value binding-"to modify.

3. Click Save (or Ctrl+s)-"Close Table Designer-" Refresh table-"Reopen Table Designer to view.

To modify the default constraint by using a T-SQL script

The default constraint modification rule must first delete the existing default constraint, and then recreate it with the new definition to modify the DEFAULT constraint using Transact-SQL.

Grammar:

--Using the database
Use database name;
Go
--Determine if the constraint you want to create already exists
if exists (select * from sysobjects where name= constraint name)
ALTER TABLE name DROP CONSTRAINT constraint name;
Go
--Add a default value constraint
ALTER TABLE name ADD CONSTRAINT constraint name default constraint value for column name;
Go

Example:

--Using the database
Use TESTSS;
Go
--Determine if the constraint you want to create already exists
if exists (select * from sysobjects where name= ' default1 ')
ALTER TABLE test1 drop constraint default1;
Go
--Add a default value constraint
ALTER TABLE TEST1 add constraint default2 default.
Go

Summarize

1. There can be only one default constraint per field.
2. If the default constraint is set to a value greater than the length allowed by the field, the allowable length of the field is truncated.
3. Cannot be added to a field with an identity attribute or timestamp.
4, if the data type of the field is a user-defined type, and the default value is bound on this data type, the default value is not allowed to be used again.

SQL Server Modify the default constraint