Oracle中的约束

迈不过友情╰ 2022-08-06 11:17 213阅读 0赞

在表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