Data Integrity Enforcement, Constraints and Indexes

Question 1

Should you use rules or declarative constraints to enforce data integrity?

Answer 1

• Declarative constraints
• Rules are not as well suited for enforcing data integrity and will be deprecated in a future version of SQL Server

SQL Server 70-461 08-02

Question 2

How are table constraints like views, tables, stored procedures, etc?

Answer 2

They are also considered database objects in SQL Server

SQL Server 70-461 08-02

Question 3

What are the 5 different types of constraints?

Answer 3

1. Primary key constraint
2. Unique constraint
3. Foreign key constraint
4. Check constraint
5. Default constraint

SQL Server 70-461 08-02

Question 4

What does a UNIQUE CONSTRAINT allow you to do?

Answer 4

Enforce uniqueness of values in a column

SQL Server 70-461 08-02

Question 5

What is automatically created when you create a UNIQUE CONSTRAINT?

Answer 5

• A unique index with the same name as constraint.
• The unique index is used to enforce the uniqueness of the column or combination of columns

SQL Server 70-461 08-02

Question 6

Can you have NULLS in a column you want to have a unique constraint?

Answer 6

Yes, but only one of the rows can be null

SQL Server 70-461 08-02

Question 7

Can PK constraints and Unique constraints be created on computed columns?

Answer 7

Yes

SQL Server 70-461 08-02

Question 8

What is the max # of columns that can be the key columns of an index?

Answer 8

16

SQL Server 70-461 08-02

Question 9

What is the max size in bytes that the key column(s) of an index can have?

Answer 9

900 bytes

SQL Server 70-461 08-02

Question 10

How does a check constraint work?

Answer 10

• You create an expression similar to a filter expression in a WHERE clause so SQL Server knows how to identify valid values and only let them in.
• You can reference other columns in the same row and use built in functions.

SQL Server 70-461 08-02

Question 11

What do you need to take into account when making a CHECK constraint column that allows nulls?

Answer 11

Make sure that the CHECK CONSTRAINT expression you create will allow nulls to get through

SQL Server 70-461 08-02

Question 12

Can you customize an error message for if the CHECK CONSTRAINT fails?

Answer 12

No. You would have to use something like a trigger to do that

SQL Server 70-461 08-02

Question 13

Can you reference the previous value of a column in the CHECK constraint expression?

Answer 13

No. You would need to use a trigger for something like that.
Example: Unit price cannot be increased bby more than 25%

SQL Server 70-461 08-02

Question 14

What does a DEFAULT CONSTRAINT do?

Answer 14

Say a record is being inserted. For the insertion, column1 is null. DEFAULT CONSTRAINT basically says if the value is NULL instead assign this other value.

SQL Server 70-461 08-02

Question 15

What happens if you don’t supply a name for a DEFAULT CONSTRAINT?

Answer 15

SQL Server will give it a machine generated name.

SQL Server 70-461 08-02

Question 16

Are all types of constraints objects?

Answer 16

• Yes. Therefore their names must be unique across the entire database.
• No two tables can have default constraints names the same thing

SQL Server 70-461 08-02

Question 17

What is the best way to enforce data integrity?

Answer 17

Create or declare constraints on base tables

SQL Server 70-461 08-02

Question 18

How are constraints applied to a table?

Answer 18

Use the CREATE TABLE or ALTER TABLE statements

SQL Server 70-461 08-02

Question 19

What is declarative data integrity?

Answer 19

When you embed methods of data integrity inside the definition of the table itself.

SQL Server 70-461 08-02

Question 20

How should you name a constraint?

Answer 20

• Since a constraint is an object it must have a unique name when compared to other db objects.
• Since it is an object but its scope is an individual table, you may want to use a naming convention that indicates type of constraint, table it applies to and key columns, if necessary.

SQL Server 70-461 08-02

Question 21

What is the standard form of the statement to add a PK?

Answer 21

CONSTRAINT pk.categories PRIMARY KEY(categoryid)

pk.categories is the name you supply for PK constraints object
categorid is the column name that the PK is on

SQL Server 70-461 08-02

Question 22

What is the standard form of the statement to add a PK to an existing table?

Answer 22

ALTER TABLE Production.Categories
ADD Constraint PK_Categories
PRIMARY KEY (categoryid)

SQL Server 70-461 08-02

Question 23

What conditions must be met to create a PK on a column?

Answer 23

1. The column cannot allow nulls
2. If you are creating a PK on a column(s) in an already existing table, the values in that column must be unique
3. There can only be one PK constraint on a table

If any of the conditions are not met, an attempt to create a PK will fail

SQL Server 70-461 08-02

Question 24

What is automatically created when you create a PK?

Answer 24

A unique index with the same name as the PK constraint

SQL Server 70-461 08-02

Question 25

How can you look up all PK constraints in a database?

Answer 25

SELECT * FROM sys.key_constraints WHERE type='PK' Just writing a sentence long enough that the text will left justify ## Footnote SQL Server 70-461 08-02

Question 26

What is the general form of a statement to add a UNIQUE CONSTRAINT?

Answer 26

**In a CREATE TABLE statement** CONSTRAINT UC_Categories UNIQUE(categoryname) **In an ALTER TABLE statement** ADD CONSTRAINT UC_Categories UNIQUE(categoryname) ## Footnote SQL Server 70-461 08-02

Question 27

What is the general form of the statement to create a foreign key?

Answer 27

``` CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid) REFERENCES Production.Categories(categoryid) ``` - **FK_Products_Categories** is the name you want to give to FK constraint object - Foreign Key(**categoryid**) is the column in foreign key table that will be FK - **Production.Categories(categoryid)** is the column in the other table that the FK will relate to, usually a PK column ## Footnote SQL Server 70-461 08-02

Question 28

What must the column in the other table that a FK relates to be?

Answer 28

The column must be one of 1. PK constraint 2. UNIQUE constraint 3. UNIQUE index Ultimately, it must have a unique index and that can be implicitly created with 1. or 2. or explicitly created with 3. ## Footnote SQL Server 70-461 08-02

Question 29

What does the WITH CHECK statement do?

Answer 29

ALTER TABLE Production.Products WITH CHECK ADD CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid) REFERENCES Production.Categories(categoryid) Makes sure that is there were any FK values not in the reference column of the other table, that the alter statement fails. ## Footnote SQL Server 70-461 08-02

Question 30

What can increase performance when working with FK?

Answer 30

- Creating a nonclustered index on the FK. Unlike PK columns, an index is not automatically created when a FK is created. - Joins may be resolved faster if FK also has an index ## Footnote SQL Server 70-461 08-02

Question 31

General form of check constraint

Answer 31

CONSTRAINT constraint_name CHECK expression **Expression Example:** (unitprice>=0) ## Footnote SQL Server 70-461 08-02

Question 32

How does WITH CHECK work?

Answer 32

When using the ALTER TABLE statemen, WITH CHECK makes sure that any records that already exist in the table are put through the constraint you are newly creating. The ALTER TABLE statement will fail if there is a violation. ## Footnote SQL Server 70-461 08-02

Question 33

What is the advantage of having a constraint in the table vs at the application level?

Answer 33

If it is at the table level it will *always* be enforced. If it is at the application level, there is always a chance that data might get into the table that shoudn't. ## Footnote SQL Server 70-461 08-02

Question 34

Why use a check constraint instead of a trigger?

Answer 34

It may perform better. ## Footnote SQL Server 70-461 08-02

Question 35

How can you get a list of CHECK CONSTRAINTS

Answer 35

``` SELECT * FROM sys.check_constraints WHERE parent_object_id=OBJECT_ID(N'Production.Products,N'U') ``` ## Footnote SQL Server 70-461 08-02

Question 36

Do you apply a DEFAULT CONSTRAINT in CREATE TABLE or ALTER TABLE statement?

Answer 36

It can be created in either but is more commonly applied during CREATE TABLE ## Footnote SQL Server 70-461 08-02

Question 37

In the CREATE TABLE statement, where is the DEFAULT CONSTRAINT applied?

Answer 37

- One place it can be applied is after a columns data type - **Example:** unitprice MONEY NOT NULL CONSTRAINT DFT\_PRODUCTS_unitprice DEFAULT(0), ## Footnote SQL Server 70-461 08-02

Question 38

General form of DEFAULT CONSTRAINT

Answer 38

``` CONSTRAINT DFT_Products_discontinued DEFAULT(0) ``` **DFT\_Products_discontinued** constraint name ## Footnote SQL Server 70-461 08-02

Question 39

How can you get a list of DEFAULT CONSTRAINTS?

Answer 39

``` SELECT * FROM sys.default_constraints WHERE parent_object_id= OBJECT_ID(N'Production.Products',N'U') ``` ## Footnote SQL Server 70-461 08-02