Oracle Database Sql Certified Associate · Free Practice Question Hard
Question 35
View and examine the following SQL exhibit.
Exhibit: 1
- CREATE TABLE orders (
- order_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
- ord_date DATE CHECK (ord_date <= SYSDATE),
- cust_id NUMBER(4)
- );
- CREATE TABLE ord_items (
- ord_no NUMBER(2),
- item_no NUMBER(3),
- qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
- CONSTRAINT DATE CHECK (ord_date <= SYSDATE),
- CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
- CONSTRAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (order_no)
- );
Given: The above command fails when executed.
Identify the reason for the failed execution.
-
A
ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.
-
B
The CHECK constraint cannot be placed on columns having the DATE data type.
-
C
The BETWEEN clause cannot be used for the CHECK constraint.
-
D
SYSDATE cannot be used with the CHECK constraint.
Reveal correct answer
Correct answer: D
Explanation
Correct option: SYSDATE cannot be used with the CHECK constraint.
The CHECK constraint in Oracle cannot reference functions like SYSDATE because it must be evaluated at the time of insertion or update using fixed column values. Since SYSDATE is dynamic and constantly changing, it cannot be enforced as a constraint in this way.
If you need to impose a restriction involving SYSDATE, consider using:
Triggers: You can write a
BEFORE INSERTorBEFORE UPDATEtrigger to enforce the condition.Default Values: Use
DEFAULT SYSDATEfor timestamp columns where needed.Computed Expressions: Instead of a constraint, check conditions within queries or application logic.
Oracle’s CHECK constraints must be deterministic, meaning they must evaluate to the same result for the same input, every time.
SYSDATEis non-deterministic — it changes constantly.As a result, you cannot use
SYSDATE(orCURRENT_DATE, etc.) in a CHECK constraint.
Example – Invalid:
- CREATE TABLE test_dates (
- hire_date DATE CHECK (hire_date >= SYSDATE) -- invalid
- );
This will throw an ORA-02436: date or system variable wrongly specified in CHECK constraint.
Alternative Approaches:
Use a trigger to validate SYSDATE-based conditions using a
BEFORE INSERT OR UPDATEtrigger:
- CREATE OR REPLACE TRIGGER trg_ord_date_check
- BEFORE INSERT OR UPDATE ON orders
- FOR EACH ROW
- BEGIN
- IF :NEW.ord_date > SYSDATE THEN
- RAISE_APPLICATION_ERROR(-20001, 'Order date cannot be in the future.');
- END IF;
- END;
What This Trigger Does:
Runs before any insert or update on the
orderstable.Checks if the new
ord_dateis greater than the current system date.If so, it raises an error and prevents the operation.
The Oracle SYSDATE function returns the current date and time of the Operating System (OS) where the Oracle Database installed.
Syntax
Since the SYSDATE function does not require any argument, you can call it without specifying the parentheses:
- SYSDATE</code>
Return value
The SYSDATE function returns the current date and time value whose type is DATE.
The format of the returned date time value depends on the value of the NLS_DATE_FORMAT parameter.
Remarks
The Oracle SYSDATE function cannot be used in the condition of a CHECK constraint.
A. The error in the execution is not related to the composite primary key constraint on ORD_NO and ITEM_NO. The issue lies in the usage of the SYSDATE function in the CHECK constraint, not in the primary key definition.
B. The CHECK constraint can be applied to columns with the DATE data type to enforce specific conditions on the values stored in those columns. Therefore, the failure of the execution is not due to the data type of the columns.
C. The BETWEEN clause can be used in a CHECK constraint to define a range of values that a column can hold. However, in this specific case, the issue is not related to the use of the BETWEEN clause.
D. The SYSDATE function is not allowed in a CHECK constraint because the value of SYSDATE changes every time the constraint is evaluated, making it unsuitable for defining a static condition.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
