Implement Database Objects: Create and modify constraints

(This is part of the Study Guide series, 70-464)
Microsoft’s Measured Skill description: This objective may include but is not limited to: create constraints on tables; define constraints; performance implications
What I see:
·         define constraints
·         create constraints
·         performance implications
Define constraints
                BOL sums this up nicely:  “Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.”  For a full description and a list of constraint types, see BOL.
Create constraints
                Creating constraints can be done one of a few ways.  Below shows a few examples:
Example
use MeasureMySkills;
go
if exists (select * from sys.tables where name = ‘ConstraintTest’)
       drop table ConstraintTest;
create table ConstraintTest
(
       id int identity(1, 1) not null
              check (id > 0)
);
go
drop table ConstraintTest;
go
create table ConstraintTest
(
       id int identity(1, 1) not null,
       constraint CK_Id check (id > 0)
);
go
drop table ConstraintTest;
go
create table ConstraintTest
(
       id int identity(1, 1) not null
);
go
alter table ConstraintTest
add constraint CK_Id
check (id > 0);
go
Performance implications
                I recommend reading this great article by Grant Fritchey on how foreign key constraints can actually help the optimizer in eliminating tables from the plan.
References
·         BOL reference on Constraints
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s