SQL Server value DEFAULT constraint

Default constraint Add rule

1, if the default value constraint is defined in the table, if the user inserts a new data row, if the row does not have the specified data, then the system assigns the default value to the column, and if we do not set the default value, the system defaults to NULL.

2. If the item in the default Value field replaces the default value of the binding (shown without parentheses), you will be prompted to unbind the default and replace it with the new default value.

3. To enter a text string, enclose the value in single quotation marks ('), and do not use double quotation marks ("), since double quotation marks are reserved for quoted identifiers.

4. To enter a numeric default value, enter a value and do not enclose the value in quotation marks.

5. To enter an object/function, enter the name of the object/function and do not enclose the name in quotation marks.

Use the SSMS Database administration tool to addDefault constraint

1, connect the database, select data Table-"Right click-" SELECT Design.

2, in the table Design window-"Select data Column-" In the Column Properties window to find the default value or binding-"Enter the default value (note The default value of the data type and input format).

3. Click the Save button (or Ctrl+s)-"Refresh table-" To open the table again to see the results.

Using T-SQL scriptsAdd default constraintWhen a table structure already exists

First, determine if there is a default constraint in the table, and if so, delete the default constraint and add it if it does not exist.

Grammar:

Use database
Go
--Determine if the default constraint exists, delete it first if it exists, add it directly if it does not exist
if exists (select * from sysobjects where name= constraint name)
ALTER TABLE name DROP CONSTRAINT constraint name;
Go
--Add a DEFAULT constraint to the specified column
ALTER TABLE name ADD CONSTRAINT constraint name default (constraint value) for column name;
Go

Example:

Use [TESTSS]
Go
--Determine if the default constraint exists, delete it first if it exists, add it directly if it does not exist
if exists (select * from sysobjects where name= ' defalut_height ')
ALTER TABLE [TESTSS]. [dbo]. [Test1] drop constraint defalut_height;
Go
--Add a DEFAULT constraint to the specified column
ALTER TABLE [TESTSS]. [dbo]. [Test1] Add constraint defalut_height default (for height);
Go

Add a DEFAULT constraint when creating a table

First determine whether the table is selected, if present, delete the table and add it, if it does not exist, add it directly.

Grammar:

--Add a DEFAULT constraint when creating a new table
--Database declaration
Use database name
Go
--If the table already exists, delete the table and create it, or create it directly if the table does not exist.
if exists (select * from sysobjects where name= table name and type = ' U ')
drop table name;
Go
--Build table Syntax declaration
CREATE TABLE table name
(
--Field declaration
Column Name Type identity (a) not NULL,
column list type) NULL,
column list type null,
column list type null,
column list type,
column list type constraint constraint name default value,
primary key clustered (column name ASC) with (Ignore_dup_key=off) on [primary]--Primary key Index declaration
) on [primary]

--Field Comment declaration
exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' column description ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' column ', @level2name =n ' list name ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' column description ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' column ', @level2name =n ' list name ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' column description ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' column ', @level2name =n ' list name ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' column description ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' column ', @level2name =n ' list name ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' column description ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' column ', @level2name =n ' list name ';

Go

Example:

--Add a DEFAULT constraint when creating a new table
--Database declaration
Use testss
Go
--If the table already exists, delete the table and create it, or create it directly if the table does not exist.
if exists (select * from sysobjects where name= ' test1 ' and type = ' U ')
drop table test1;
Go
--Build table Syntax declaration
CREATE TABLE Test1
(
--Field declaration
ID int Identity (a) is not NULL,
name nvarchar () NULL,
sex nvarchar () null,
Age nvarchar () null,
ClassID int,
height int constraint default_he default 166,
primary key clustered (ID ASC) with (Ignore_dup_key=off) on [primary]--Primary key Index declaration
) on [primary]

--Field Comment declaration
exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' ID primary key ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' test1 ', @level2type =n ' COLUMN ', @level2name =n ' id ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' name ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' test1 ', @level2type =n ' COLUMN ', @level2name =n ' name ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' sex ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' test1 ', @level2type =n ' COLUMN ', @level2name =n ' sex ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' age ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' test1 ', @level2type =n ' COLUMN ', @level2name =n ' age ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' class ID ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' test1 ', @level2type =n ' COLUMN ', @level2name =n ' ClassID ';

Go

Advantages and disadvantages of default constraints

Advantages:

1, using the default value can reduce the amount of code, the new data can not write the new default column, the new operation is always the default padding.

2, more conducive to statistical and analysis, as well as convenient program logic operation.

Disadvantages:

1. Use a non-null default value, which takes up more storage space.

SQL Server value DEFAULT constraint