Evaluate the following SQL statements: Exhibit: Exhibit: 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:
Perfect!
Correct answer is: B
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pd PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qtd NUMBER(4) CHECK(qtd BETWEEN 100 AND 200),
expiry_date DATE CHECK(expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY(ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
Table ORDERS criado.
Erro a partir da linha : 6 no comando –
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qtd NUMBER(4) CHECK(qtd BETWEEN 100 AND 200),
expiry_date DATE CHECK(expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY(ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no))
Relatório de erros –
Erro de SQL: ORA-00604: ocorreu um erro no nível 1 SQL recursivo
ORA-06502: PL/SQL: erro numérico ou de valor
ORA-06512: em line 8
ORA-02436: data ou variável do sistema especificada incorretamente na restrição CHECK
00604. 00000 – “error occurred at recursive SQL level %s”
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.