Term
| What is the purpose of a constraint? |
|
Definition
| Enforce business rules and data consistency. |
|
|
Term
|
Definition
| Defines the column(s) that uniquely identify each row of data in the table. |
|
|
Term
| What is the default type of primary key? |
|
Definition
|
|
Term
| Does a primary key affect how data is stored in a table? |
|
Definition
| YES. It stores the data sorted by the primary key. |
|
|
Term
| When is compression applied to the primary key for a table that is compressed? |
|
Definition
| When the table is rebuilt. |
|
|
Term
| What is the purpose of a foreign key? |
|
Definition
| To implement referential integrity between tables. |
|
|
Term
| Should you use the CASCADE option on a foreign key? |
|
Definition
NO. The cascade option can cause unintended deletions in other tables when that primary key value is deleted.
It is better to orphan data than delete it. |
|
|
Term
| What are the types of constraints? |
|
Definition
| UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY, NOT NULL |
|
|
Term
| What is the purpose of a UNIQUE constraint? |
|
Definition
| Restrict a column to contain unique values in that table. |
|
|
Term
| Can a primary key contain NULLs? Can a column with a UNIQUE constraint contain NULLs? |
|
Definition
- NO. Primary keys cannot have any NULL values.
- NO. Unique constraints can have exactly one NULL in that column.
|
|
|
Term
| Under what operations does a DEFAULT constraint apply? |
|
Definition
| New rows added under BCP, BULK INSERT, and INSERT |
|
|
Term
| What action does a DEFAULT constraint provide? |
|
Definition
| Gives a default value for that column when a value is not otherwise provided. |
|
|
Term
| Can CHECK constraints compare values to values in another column? |
|
Definition
A) YES, for a TABLE-LEVEL CHECK constraint, but only within the same table.
B) A COLUMN-LEVEL CHECK constraints cannot reference other columns in the table, and the value must fall within a predefined range of values. |
|
|
Term
| What operators can be used for CHECK constraints? |
|
Definition
|
|
Term
| Can CHECK constraints use wildcards? |
|
Definition
| YES. % and _ are allowed. |
|
|
