表的约束与索引

一、实体完整性（通过主键）

1.创建表

   create table nn(id varchar2(12),name varchar2(20));

 2.增加约束

  alter table nn add constraint pk_nn primary key(id);

 

二、参照完整性（通过外键1）

 1.创建表

   SQL> create table mm(id varchar2(12) not null primary key,
  2  mname varchar2(20),nid varchar2(12));

表已创建。

 

2.增加外键约束

SQL> alter table mm add constraint fk_mn foreign key(nid) references nn(id);

 

SQL> select * from nn;

未选定行

SQL> insert into nn values('1','lili');

已创建 1 行。

SQL> insert into mm values('1','sal','2');
insert into mm values('1','sal','2')
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.FK_MN) - 未找到父项关键字

SQL> insert into mm values('1','sal','1');

已创建 1 行。

SQL>

 

3.增加check约束

 

   SQL> alter table mm add constraint ck_mm_check check(mname='男' or mname='女');
alter table mm add constraint ck_mm_check check(mname='男' or mname='女')
                              *
第 1 行出现错误:
ORA-02293: 无法验证 (SCOTT.CK_MM_CHECK) - 违反检查约束条件

SQL> select * from mm;

ID           MNAME                NID
------------ -------------------- ------------
1            sal                  1

SQL> update mm set mname ='男';

已更新 1 行。

SQL> alter table mm add constraint ck_mm_check check(mname='男' or mname='女');

表已更改。

SQL>

 

检查

SQL> insert into mm values(2,'nihao','1');
insert into mm values(2,'nihao','1')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.CK_MM_CHECK)

SQL>

 

三、查看约束

 SQL> select table_name,constraint_name,constraint_type from user_constraints;

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
MM                             CK_MM_CHECK                    C
MM                             FK_MN                          R
MM                             SYS_C005394                    C
EMP                            FK_DEPTNO                      R
DEPT                           PK_DEPT                        P
EMP                            PK_EMP                         P
NN                             PK_NN                          P
MM                             SYS_C005395                    P

已选择8行。

SQL>

 

四、索引

1.建立索引

  SQL> create index mm_index on mm(nid);(B树索引)

索引已创建。

 
SQL> select * from mm where nid = '001';

 

2.建立位图索引（适合有大量重复数据，也就说数据量大，但数据值很少，多为重复）

SQL> create bitmap index my_bitmap_index on mm(mname);

索引已创建。

SQL>

 

3.唯一索引

 SQL> create unique index mm_unique_index on nn(name);

索引已创建。

 

 

 -- 查询被指定表引用的父表
select
c.owner||'.'||c.table_name child_table,
c.constraint_name,
r.owner||'.'||r.table_name parent_table
from dba_constraints c,dba_constraints r
where c.owner=r.r_owner and c.constraint_name=r.r_constraint_name
and c.owner=upper('&child_owner') and c.table_name=upper('&child_table');

-- 查询引用指定表的子表
select
c.owner||'.'||c.table_name child_table,
c.constraint_name,
r.owner||'.'||r.table_name parent_table
from dba_constraints c,dba_constraints r
where c.owner=r.r_owner and c.constraint_name=r.r_constraint_name
and r.owner=upper('&parent_owner') and r.table_name=upper('&parent_table');