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

·         BOL reference on CREATE TABLE

·         Grant Fritchey’s blog post: Do Foreign Key Constraints Help Performance?

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.