OCP:051-----什么约束在建表的时候会传递？

Oracle支持的约束类型：

         1.UNIQUE

          2.NOT NULL

          3.PRIMARY KEY

           4.FOREIGN KEY

           5.CHECK

 

第一步：建表

SQL> create table customers(cust_id varchar2(10) constraint pk_customer primary key);

Table created.

SQL> create table times(time_id date constraint pk_times primary key);

Table created.

SQL> create table products(prod_id number(6) constraint pk_products primary key);

Table created.

SQL> create table sales(prod_id number(6) constraint fk_sales_prod references products(prod_id),cust_id varchar2(10) constraint fk_sales_cust references customers(cust_id),
  2  time_id date constraint fk_sales_time references times(time_id),order_date date not null,test_uniq number unique,test_check number check(test_check >1));

Table created.

SQL> create table new_sales(prod_id,cust_id,order_date default sysdate,test_uniq,test_check) as select prod_id,cust_id,order_date,test_uniq,test_check from sales;

Table created.

SQL> desc new_sales;
 Name                                                Null?            Type
 ----------------------------------------- --------       ----------------------------
 PROD_ID                                                             NUMBER(6)
 CUST_ID                                                             VARCHAR2(10)
 ORDER_DATE                           NOT NULL     DATE                               -------NOT NULL约束传递了。
 TEST_UNIQ                                                         NUMBER
 TEST_CHECK                                                     NUMBER

SQL> select table_name,constraint_name,constraint_type,search_condition,r_constraint_name from user_constraints  where table_name='NEW_SALES';

TABLE_NAME                     CONSTRAINT_NAME                C SEARCH_CONDITION                                                                 R_CONSTRAINT_NAME
------------------------------ ------------------------------ - -------------------------------------------------------------------------------- ------------------------------
NEW_SALES                      SYS_C006947                    C "ORDER_DATE" IS NOT NULL

 

本来以为C 是check约束，后来发现，是规定NOT NULL 的。。。。

 

happy结局：NOT NULL 约束在建表的时候会传递。