Evaluate the following SQL commands:
The command to create a table fails. Identify the two reasons for the SQL statement failure?
A.
You cannot use SYSDATE in the condition of a check constraint.
B.
You cannot use the BETWEEN clause in the condition of a check constraint.
C.
You cannot use the NEXTVAL sequence value as a default value for a column.
D.
You cannot use ORD_NO and ITEM_NO columns as a composite primary key because
ORD_NO is also the foreign key.
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),
A,C
A, C
Oracle check constraint validates incoming columns at row insert time.
It has some limitations:
1. Subqueries cannot be used within your check constraints.
2. It is able to reference another column but not reference columns from other tables.
3. Most importantly, SYSDATE, CURRVAL, NEXTVAL, LEVEL, ROWID, UID, USER or USERENV cannot be referenced with Oracle check constraint.
Restrictions on Sequence Values
You cannot use CURRVAL and NEXTVAL in the following constructs:
A subquery in a DELETE, SELECT, or UPDATE statement.
A query of a view or of a materialized view.
A SELECT statement with the DISTINCT operator.
A SELECT statement with a GROUP BY clause or ORDER BY clause.
A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator.
The WHERE clause of a SELECT statement.
The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement.
The condition of a CHECK constraint.