SQL expert (1z0-047)的题库里有这样一道题,我觉得挺有意思的。
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the existing CUSTOMER_ID column?
A. ALTER TABLE orders ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
B. ALTER TABLE orders MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;
C. ALTER TABLE orders MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
D. ALTER TABLE orders ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
粗一看觉得答案是A。D肯定是错的,因为customer_id这个字段已经存在了。觉得BC错是因为我压根就没见过MODIFY CONSTRAINT这种用法。不是只有drop和add constraint吗?
答案却是B。
这是套用了ALTER TABLE table MODIFY column CONSTRAINT constraint inline_constraint的用法。
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#i2103924
A为什么错了?因为ADD CONSTRAINT的用法是ALTER TABLE table ADD CONSTRAINT constraint out_of_line_constraint。
inline_constraint和out_of_line_constraint有什么区别?
http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52180
主要区别就在于out_of_line_constraint不支持NULL/NOT NULL。其他的什么PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY都支持,当然格式有点不一样。
关于C,其实MODIFY CONSTRAINT是有的,用法是ALTER TABLE table MODIFY CONSTRAINT constraint_state。什么是constraint_state?
就是INITIALLY IMMEDIATE/DEFERRED, ENABLE/DISABLE, VALIDATE/NOVALIDATE这些东西,所以这个用法套不上的。
No comments:
Post a Comment