You are a developer on an instance of SQL Server 2008. You are designing an Event table. The EventDate column must contain a value. In addition, for each event, the EventDate must be a date between the StartDate and EndDate.
Which two constructs should you use? (Choose two. Each correct answer represents a portion of the answer.)
A. a DEFAULT definition
B. a NOT NULL constraint
C. a table-level CHECK constraint
D. a UNIQUE constraint
E. a column-level CHECK constraint
Correct Answer: BC
Explanation/Reference:
A NOT NULL constraint is used to ensure that a non-null value is specified for a column when a new row is added to a table or when a row in a table is updated. To ensure that a non-null value will be specified for the EventDate column for a new row in the Event table, you should specify a NOT NULL constraint on the EventDate column. If an attempt is made to insert a row into the Event table without specifying an event date, the insert will fail and an error message will be returned.
To ensure the event date is within the desired date range, you can use a table-level CHECK constraint. A CHECK constraint is used to restrict the data that is allowed for a column to specific values. A CHECK constraint consists of a Boolean expression that evaluates to either TRUE or FALSE. If the expression evaluates to TRUE, the value is allowed for the column, and if the expression evaluates to FALSE, the value is not allowed for the column. CHECK constraints can be defined at the table level or column level, but only CHECK constraints defined at the table level can use columns other than the constrained column in the constraint expression. In this scenario, you need to create a CHECK constraint on the EventDate column and use other table columns in the constraint expression. Therefore, you must use a table-level constraint.
You should not use a DEFAULT definition. A DEFAULT definition is used to specify a value that should be assigned to a column when a user adds a row without specifying a value for that column. A DEFAULT definition can include constants and operators, but cannot contain column names.
You should not use a UNIQUE constraint. A UNIQUE constraint is used to ensure that a column contains unique, non-duplicate values. Unlike a PRIMARY KEY constraint, which also ensures non-duplicate values, a UNIQUE constraint allows a single row to have a NULL value for the column. When you define a column with a UNIQUE constraint, SQL Server automatically creates a unique index on the column and uses this index to prevent duplicate values.
You should not use a column-level CHECK constraint. In this scenario, the constraint must reference the EventDate, StartDate, and EndDate columns. To create a constraint that accesses columns other than the constrained column, you must create a table-level CHECK constraint.
