Restrictions in Oracle and 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_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
-- View Constraints
Create or replace view v_test
As select * from test where col3 = 'China' with check option; -- When you insert, modify, or delete a view in the future, the condition col3 = 'China' is automatically added.
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
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