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