Evaluate the following SQL statements:
The above command fails when executed. What could be the reason?
A.
The BETWEEN clause cannot be used for the CHECK constraint
B.
SYSDATE cannot be used with the CHECK constraint
C.
ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also
the FOREIGN KEY
D.
The CHECK constraint cannot be placed on columns having the DATE data type
Explanation:
CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the
same constructs as the query conditions, with the following exceptions:
References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition.
There is no limit to the number of CHECK constraints that you can define on a column.
CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees
(…
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),
B
Conditions of CHECK constraints cannot contain the following constructs:
•Subqueries and scalar subquery expressions
•Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)
•Calls to user-defined functions
•Dereferencing of REF columns (for example, using the DEREF function)
•Nested table columns or attributes
•The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
•Date constants that are not fully specified
Correct Answer:
B. SYSDATE cannot be used with the CHECK constraint