31.Evaluate the following SQL commands:

31.Evaluate the following SQL commands:
SQL>CREATE SEQUENCE ord_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCYCLE;

SQL>CREATE TABLE ord_items(
            ord_no NUMBER(4) DEFAULT ord_seq.NEXTVAL NOT NULL
            ,item_no NUMBER(3)
            ,qty NUMBER(3) CHECK (qty 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)
);


The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)
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.
答案：AC
A：正确， 参考30题
B：错误， 参考30题
C：正确，提示ORA-00984 column not allowed here 错误

那如果想让序列作为一个表的自增长列怎么处理哪？

SQL> create table test (a int );
Table created

SQL> create trigger test_sequence
  2  before insert on test
  3  for each row 
  4      when (new.a is null)
  5  begin
  6      select ord_seq.nextval into :new.a from dual;
  7  end;
  8  /
Trigger created

SQL> insert into test values(null);
1 row inserted

SQL> insert into test values(null);
1 row inserted

SQL> select * from test;
                                      A
---------------------------------------
                                    120
                                    130

SQL> 

这里注意new伪记录的使用方法，在when中不需要使用：，其他地方都需要使用：
--那序列是否可以用于check约束？

SQL> create table test (a int ,constraints ck_a check(a<ord_seq.nextval));
create table test (a int ,constraints ck_a check(a<ord_seq.nextval))
ORA-00904: "ORD_SEQ"."NEXTVAL": 标识符无效

--因此：序列不能用于默认值和check约束，通过30题知道sysdate可以用于默认值，但是不能用于check约束
D：错误， 参考30题