Oracle中的約束，Oracle約束

Oracle中的約束，Oracle約束
在表ALL_CONSTRAINTS中約束類型CONSTRAINT_TYPE：
C　(check constraint on a table)--約束，如not null,check
P　(primary key)--主鍵
U　(unique key) --唯一鍵約束
R　(referential integrity)--外鍵
--下面兩個是作用於視圖
V　(with check option, on a view)

O　(with read only, on a view)

有幾個類型還是不理解，於是做了個實驗加深印象：

create table test(
  t_id varchar2(20),
  col2 varchar2(20),
  col3 varchar2(20),
  col4 varchar2(20),
  col5 varchar2(20),
  col6 varchar2(20)  
);
alter table TEST add constraint pk_test primary key (t_id);
alter table TEST add constraint u_test unique (COL2);
alter table TEST modify COL3 not null;

create table test_detail(
 t_d_id varchar2(20),
 t_id varchar2(20),
 t_num number(10),
 detail varchar2(20)
);
alter table test_detail add constraint pk_test_detail primary key (t_d_id);
alter table test_detail add constraint fk_test foreign key (T_ID)
  references test (T_ID);
alter table test_detail add constraint CK_t_num check(t_num between 1 and 30);

--查詢

col table_name format a15;   
col column_name format a8;  
col constraint_name format a15;  
col constraint_type format a5;   
col search_condition format a25;   
col r_owner format a10;  
col r_constraint_name format a10;   
select c.table_name,
       s.column_name,
       c.constraint_name,
       c.constraint_type,
       c.search_condition,
       c.r_owner,
       c.r_constraint_name
  from user_cons_columns s, user_constraints c
 where c.table_name = s.table_name
   and c.constraint_name = s.constraint_name;

TABLE_NAME      COLUMN_N CONSTRAINT_NAME CONST SEARCH_CONDITION          R_OWNER    R_CONSTRAI
--------------- -------- --------------- ----- ------------------------- ---------- ----------
TEST            COL2     U_TEST          U
TEST            COL3     SYS_C008146     C     "COL3" IS NOT NULL
TEST            T_ID     PK_TEST         P
TEST_DETAIL     T_D_ID   PK_TEST_DETAIL  P
TEST_DETAIL     T_ID     FK_TEST         R                               TEST       PK_TEST
TEST_DETAIL     T_NUM    CK_T_NUM        C     t_num between 1 and 30

--視圖的約束

create or replace view v_test
as select * from test where col3='CHINA' with check option; --以後對該視圖插入、修改、刪除操作時，會自動加上col3='CHINA'的條件
select s.table_name, s.constraint_name, s.constraint_type
  from user_constraints s
 where s.table_name in ('V_TEST', 'V_TEST_DETAIL');
create or replace view v_test_detail as
    select * from test_detail with read only;

select s.table_name, s.constraint_name, s.constraint_type
  from user_constraints s
 where s.table_name in ('V_TEST', 'V_TEST_DETAIL');

TABLE_NAME      CONSTRAINT_NAME CONST
--------------- --------------- -----
V_TEST          SYS_C008150     V
V_TEST_DETAIL   SYS_C008151     O