Constraints in Oracle
Constraint type CONSTRAINT_TYPE in Table ALL_CONSTRAINTS:
C (check constraint on a table) -- constraints, such as not null, check
P (primary key) -- primary key
U (unique key) -- unique key constraint
R (referential integrity) -- foreign key
-- The following two operations apply to the view
V (with check option, on a view)
O (with read only, on a view)
There are several types that I still don't understand, so I made an experiment to deepen my impression:
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); -- Query 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_namefrom user_cons_columns s, user_constraints cwhere c. table_name = s. table_nameand c. constraint_name = s. constraint_name; TABLE_NAME COLUMN_N limit CONST SEARCH_CONDITION R_OWNER limit -------- limit ----- limit ------------ TEST COL2 U_TEST UTEST COL3 collate C "COL3" is not valid T_ID PK_TEST 1_t_d_id limit T_ID FK_TEST r test limit T_NUM limit C t_num between 1 and 30 -- create or replace view v_testas select * from test where col3 = 'China' with check option; -- When you insert, modify, or delete a view, the select s condition of col3 = 'China' is automatically added. table_name, s. constraint_name, s. constraint_typefrom user_constraints swhere s. table_name in ('v _ test', 'v _ TEST_DETAIL '); create or replace view v_test_detail asselect * from test_detail with read only; select s. table_name, s. constraint_name, s. constraint_typefrom user_constraints swhere s. table_name in ('v _ test', 'v _ TEST_DETAIL '); TABLE_NAME CONSTRAINT_NAME CONST --------------- V_TEST SYS_C008150 VV_TEST_DETAIL SYS_C008151 O