Constraints in Oracle

Source: Internet
Author: User

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

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.