SQL Server – Default Constraints & Explicit Values

Background

Constraints can be a powerful tool in enforcing data rules per SQL Server.

 

Constraints

Here are some of the rules:

  1. Not Null
    • Rule
      • Ensure that a column’s value can not be null
    • Boundary
      • Single Column
    • Validated
      • Validated during target’s table data insert and update
  2. Unique
    • Rule
      • Ensure that the values on one or more column has to be unique
      • No other Row can have same exact values
    • Boundary
      • One or more columns
    • Validated
      • Validated during target’s table data insert and update
  3. Primary Key
    • Rule
      • Ensure that the values on one or more column has to be unique
      • No other Row can have same exact values
    • Boundary
      • One or more columns
      • None of the Columns can be defined as null
      • Only a single primary key can be defined on a table
    • Validated
      • Validated during target’s table data insert and update
  4. Foreign Key
    • Rule
      • Ensure that co-related values exists in the referenced table
    • Boundary
      • One or more columns
    • Validated
      • Validated during target’s table data insert and update
      • Also, validated during referenced table update and deletes

 

 

Constraint – Not Null

Let us talk about the Not Null constraint.

Default Constraint

Definition

Column Definition

A default constraint can be defined during table declaration

SQL
declare @tblConstraintDefault TABLE
(
	  [id] int not null	identity(1,1)

	, [dob] datetime not null
		default '1900-01-01'

	, [dtInsert] datetime
               default getdate()
)

 

Table Constraint

Default Constraints can be defined post table creation

SQL

use [tempdb]
go

set xact_abort on
set nocount on
go

/*
	Drop Table if it exists
*/
if object_id('[tempdb]..#person') is not null
begin
	drop table #person
end
go

/*
	Create Table
*/
create table #person
(
	  [id] int not null	identity(1,1)

	, [dob] datetime not null
		--default '1900-01-01'

	, [dtInsert] datetime
)
go

/*
	add constaint post table declaration
*/
alter table #person
	add default
		'1900-01-01' 
		for [dob]


if object_id('[tempdb]..#person') is not null
begin

	drop table #person

end
go

 

Adding data

Adding data specify value

SQL

insert into @tblConstraintDefault
([dob])
select getdate()

 

Adding data default values

SQL

insert into @tblConstraintDefault
default values

 

Adding data – Explicitly specify null for dob

SQL
insert into @tblConstraintDefault
([dob])
select null
Error
Error – Textual
Msg 515, Level 16, State 2, Line 40
Cannot insert the value NULL into column 'dob', table '@tblConstraintDefault'; column does not allow nulls. INSERT fails.

Summary

Moral of the story.

If you are expecting default constraints to help or save you, make sure that you have not passed in column and value for not null column.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s