Restrictions in Oracle and Oracle

Source: Internet
Author: User

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

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.