Chapter 13 索引与约束(Indexes and Constraints)
create table empcon
as
select *
from emp;
select *
from empcon;
SQL> desc empcon;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) Y
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
create index empcon_ename_idx
on empcon(ename);
select index_name, index_type, table_name, uniqueness
from user_indexes;
select index_name, table_name, column_name,column_position
from user_ind_columns
where upper(column_name) like upper('ename');
create index empcon_job_sal_idx
on empcon(job,sal);
select index_name, table_name, column_name,column_position
from user_ind_columns
where upper(column_name) like upper('job');
create index empcon_salgt_idx
on empcon(sal - 2000);
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_owner = 'SCOTT';
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_name = 'EMPCON';
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan
explain plan for
select ename, job, sal, comm, deptno
from empcon
where (sal - 2000) < 0 ;
select id, operation, options, object_name, position
from plan_table;
truncate table plan_table;
explain plan for
select empno, ename, job, sal
from empcon
where ename like 'J%';
drop index empcon_job_sal_idx;
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_name = upper('empcon_job_sal_idx');
select index_name, index_type, table_name, uniqueness
from dba_indexes
where owner = upper('SCOTT');
select index_name, index_type, table_name, uniqueness
from dba_indexes
where table_name = 'EMPCON';
create table deptcon(
deptno number(3),
dname varchar(15) not null,
loc varchar(20)
);
select *
from deptcon;
SQL> desc deptcon;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(3) Y
DNAME VARCHAR2(15)
LOC VARCHAR2(20) Y
insert into deptcon (deptno, dname, loc)
values
(10, 'ACCOUNTING', 'BEIJING')
select * from deptcon;
insert into deptcon (deptno, dname, loc)
values
(20, '', 'GUANGZHOU');
insert into deptcon (deptno, dname, loc)
values
(20, 'UNKNOWN', 'GUANGZHOU');
select * from deptcon;
update deptcon
set dname = null
where deptno = 20;
update deptcon
set dname = 'ACCOUNTING'
where deptno = 20;
//约束
select owner, constraint_name,constraint_type, table_name
from user_constraints;
C: 代表Check 约束和not null（非空约束）
P: 代表Primary key
R: 代表REFERENTIAL INTEGRITY,就是外键(FOREIGN KEY)
U: 代表UNIQUE(唯一约束)
select owner, constraint_name, table_name, column_name
from user_cons_columns
where table_name like upper('deptcon');
alter table deptcon
drop constraint SYS_C005438;
select owner, constraint_name, table_name,column_name
from user_cons_columns;
alter table deptcon
add constraint deptcon_dname_uk unique(dname);
select * from deptcon;
delete from deptcon
where deptno = 20;
select * from deptcon;
alter table deptcon
add constraint deptcon_dname_uk unique(dname);
select owner, constraint_name, constraint_type,table_name,
search_condition
from user_constraints;
insert into deptcon (deptno, dname, loc)
values(20,NULL,'牛街')
UK: UNIQUE KEY
PK: PRIMARY KEY
FK: FOREIGN KEY
CK: CHECK
NN: NOT NULL
insert into deptcon (deptno, dname, loc)
values(20,NULL,'狼山镇')
create table person(
id varchar2(10),
name varchar2(20),
gender char(1),
age number,
constraint person_gender_ck
check(gender = 'F'),
constraint person_age_ck
check(age between 18 and 35)
);
SQL> desc person;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
ID VARCHAR2(10) Y
NAME VARCHAR2(20) Y
GENDER CHAR(1) Y
AGE NUMBER Y
select owner, constraint_name, constraint_type, table_name,
search_condition
from user_constraints
where table_name = 'PERSON';
insert into person(id,name,gender,age)
values(1001,'白小丫','F',22)
select * from person;
insert into person(id,name,gender,age)
values(1002,'王老五','M',22)
insert into person(id,name,gender,age)
values(1002,'李渊源','F',36);
insert into person(id,name,gender,age)
values(1002,'张三','F',17);
select * from deptcon;
alter table deptcon
add constraint deptcon_deptno_pk
primary key (deptno);
update deptcon
set deptno = 30
where loc = '狼山镇';
select * from deptcon;
select owner, constraint_name, constraint_type, table_name,
search_condition
from user_constraints
where table_name = 'DEPTCON';
select owner, constraint_name, table_name, column_name, position
from user_cons_columns
where table_name = 'DEPTCON';
insert into deptcon (deptno, dname, loc)
values(NULL, '公关','方圆广场');
insert into deptcon (deptno, dname, loc)
values(88, '公关','方圆广场');
select * from deptcon;
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_name = 'DEPTCON';
select index_name, table_name, column_name,column_position
from user_ind_columns
where table_name = 'DEPTCON';
create table empcon
as
select * from
from emp_dml;
select * from empcon;
alter table empcon
add constraint empcon_deptno_fk
foreign key (deptno) references deptcon (deptno);
select distinct deptno
from empcon;
select *
from deptcon;
insert into deptcon
values(88,'保卫','狮子街');
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_name = 'EMPCON';
select index_name, table_name, column_name,column_position
from user_ind_columns
where table_name = 'EMPCON';
select owner, constraint_name, constraint_type, table_name,
search_condition
from user_constraints
where table_name = 'EMPCON';
select owner, constraint_name, table_name, column_name, position
from user_cons_columns
where table_name = 'EMPCON';
insert into empcon(empno,ename,mgr,hiredate,sal,comm,deptno)
values (1010,'白小丫',7839,Sysdate,5000,1500,10);
insert into empcon(empno,ename,mgr,hiredate,sal,comm,deptno)
values (1010,'李渊源',7839,Sysdate,5000,1500,15);
insert into empcon(empno,ename,mgr,hiredate,sal,comm,deptno)
values (1010,'李渊源',7839,Sysdate,5000,1500,10);
select *
from empcon
where sal >= 5000;
delete from deptcon
where deptno = '88';
update deptcon
set deptno = 44
where deptno = 88;
select * from deptcon where deptno = 88;
update empcon
set deptno = 44
where deptno = 88;
select *
from empcon;
update empcon
set deptno = null
where deptno = 88;
update deptcon
set deptno = 44
where deptno = 88;
update empcon
set deptno = 44
where deptno is NULL;
select *
from empcon;
select *
from empcon
where deptno = 44;
drop table deptcon;
truncate table deptcon;
alter table deptcon
drop column deptno;
alter table empcon
drop constraint empcon_deptno_fk;
--on delete set null; 当主表中的一行数据被删除时，Oracle系统会自动地将所有从表中依赖于他的数据记录的外键改为空。
alter table empcon
add constraint empcon_deptno_fk
foreign key (deptno) references deptcon(deptno)
on delete set null;
select *
from deptcon
order by deptno;
select empno, ename, job, sal, deptno
from empcon
where deptno > 20
order by deptno;
delete from deptcon
where deptno = 44;
select * from deptcon;
select empno, ename, job, sal, deptno
from empcon
where deptno > 20
order by deptno;
select empno, ename, job, sal, deptno
from empcon
where deptno > 20
or deptno is null;
rollback;
alter table empcon
drop constraint empcon_deptno_fk;
--级联删除子表的数据
alter table empcon
add constraint empcon_deptno_fk
foreign key (deptno) references deptcon (deptno)
on delete cascade;
delete from deptcon
where deptno = 44;
select *
from deptcon;
select empno, ename, sal, job, deptno
from empcon;
--约束的维护
select owner, constraint_name,constraint_type, table_name,
r_constraint_name, status
from user_constraints
where table_name = 'EMPCON';
select owner, constraint_name,constraint_type, table_name,
r_constraint_name, status
from user_constraints
where table_name = 'DEPTCON';
alter table deptcon
disable constraint deptcon_deptcon_pk;
alter table deptcon
disable constraint deptcon_deptcon_pk casede;
alter table deptcon
enable constraint deptcon_deptcon_pk;
create table empcon
as
select *
from emp;
select *
from empcon;
SQL> desc empcon;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) Y
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
create index empcon_ename_idx
on empcon(ename);
select index_name, index_type, table_name, uniqueness
from user_indexes;
select index_name, table_name, column_name,column_position
from user_ind_columns
where upper(column_name) like upper('ename');
create index empcon_job_sal_idx
on empcon(job,sal);
select index_name, table_name, column_name,column_position
from user_ind_columns
where upper(column_name) like upper('job');
create index empcon_salgt_idx
on empcon(sal - 2000);
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_owner = 'SCOTT';
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_name = 'EMPCON';
SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan
explain plan for
select ename, job, sal, comm, deptno
from empcon
where (sal - 2000) < 0 ;
select id, operation, options, object_name, position
from plan_table;
truncate table plan_table;
explain plan for
select empno, ename, job, sal
from empcon
where ename like 'J%';
drop index empcon_job_sal_idx;
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_name = upper('empcon_job_sal_idx');
select index_name, index_type, table_name, uniqueness
from dba_indexes
where owner = upper('SCOTT');
select index_name, index_type, table_name, uniqueness
from dba_indexes
where table_name = 'EMPCON';
create table deptcon(
deptno number(3),
dname varchar(15) not null,
loc varchar(20)
);
select *
from deptcon;
SQL> desc deptcon;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(3) Y
DNAME VARCHAR2(15)
LOC VARCHAR2(20) Y
insert into deptcon (deptno, dname, loc)
values
(10, 'ACCOUNTING', 'BEIJING')
select * from deptcon;
insert into deptcon (deptno, dname, loc)
values
(20, '', 'GUANGZHOU');
insert into deptcon (deptno, dname, loc)
values
(20, 'UNKNOWN', 'GUANGZHOU');
select * from deptcon;
update deptcon
set dname = null
where deptno = 20;
update deptcon
set dname = 'ACCOUNTING'
where deptno = 20;
//约束
select owner, constraint_name,constraint_type, table_name
from user_constraints;
C: 代表Check 约束和not null（非空约束）
P: 代表Primary key
R: 代表REFERENTIAL INTEGRITY,就是外键(FOREIGN KEY)
U: 代表UNIQUE(唯一约束)
select owner, constraint_name, table_name, column_name
from user_cons_columns
where table_name like upper('deptcon');
alter table deptcon
drop constraint SYS_C005438;
select owner, constraint_name, table_name,column_name
from user_cons_columns;
alter table deptcon
add constraint deptcon_dname_uk unique(dname);
select * from deptcon;
delete from deptcon
where deptno = 20;
select * from deptcon;
alter table deptcon
add constraint deptcon_dname_uk unique(dname);
select owner, constraint_name, constraint_type,table_name,
search_condition
from user_constraints;
insert into deptcon (deptno, dname, loc)
values(20,NULL,'牛街')
UK: UNIQUE KEY
PK: PRIMARY KEY
FK: FOREIGN KEY
CK: CHECK
NN: NOT NULL
insert into deptcon (deptno, dname, loc)
values(20,NULL,'狼山镇')
create table person(
id varchar2(10),
name varchar2(20),
gender char(1),
age number,
constraint person_gender_ck
check(gender = 'F'),
constraint person_age_ck
check(age between 18 and 35)
);
SQL> desc person;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
ID VARCHAR2(10) Y
NAME VARCHAR2(20) Y
GENDER CHAR(1) Y
AGE NUMBER Y
select owner, constraint_name, constraint_type, table_name,
search_condition
from user_constraints
where table_name = 'PERSON';
insert into person(id,name,gender,age)
values(1001,'白小丫','F',22)
select * from person;
insert into person(id,name,gender,age)
values(1002,'王老五','M',22)
insert into person(id,name,gender,age)
values(1002,'李渊源','F',36);
insert into person(id,name,gender,age)
values(1002,'张三','F',17);
select * from deptcon;
alter table deptcon
add constraint deptcon_deptno_pk
primary key (deptno);
update deptcon
set deptno = 30
where loc = '狼山镇';
select * from deptcon;
select owner, constraint_name, constraint_type, table_name,
search_condition
from user_constraints
where table_name = 'DEPTCON';
select owner, constraint_name, table_name, column_name, position
from user_cons_columns
where table_name = 'DEPTCON';
insert into deptcon (deptno, dname, loc)
values(NULL, '公关','方圆广场');
insert into deptcon (deptno, dname, loc)
values(88, '公关','方圆广场');
select * from deptcon;
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_name = 'DEPTCON';
select index_name, table_name, column_name,column_position
from user_ind_columns
where table_name = 'DEPTCON';
create table empcon
as
select * from
from emp_dml;
select * from empcon;
alter table empcon
add constraint empcon_deptno_fk
foreign key (deptno) references deptcon (deptno);
select distinct deptno
from empcon;
select *
from deptcon;
insert into deptcon
values(88,'保卫','狮子街');
select index_name, index_type, table_name, uniqueness
from user_indexes
where table_name = 'EMPCON';
select index_name, table_name, column_name,column_position
from user_ind_columns
where table_name = 'EMPCON';
select owner, constraint_name, constraint_type, table_name,
search_condition
from user_constraints
where table_name = 'EMPCON';
select owner, constraint_name, table_name, column_name, position
from user_cons_columns
where table_name = 'EMPCON';
insert into empcon(empno,ename,mgr,hiredate,sal,comm,deptno)
values (1010,'白小丫',7839,Sysdate,5000,1500,10);
insert into empcon(empno,ename,mgr,hiredate,sal,comm,deptno)
values (1010,'李渊源',7839,Sysdate,5000,1500,15);
insert into empcon(empno,ename,mgr,hiredate,sal,comm,deptno)
values (1010,'李渊源',7839,Sysdate,5000,1500,10);
select *
from empcon
where sal >= 5000;
delete from deptcon
where deptno = '88';
update deptcon
set deptno = 44
where deptno = 88;
select * from deptcon where deptno = 88;
update empcon
set deptno = 44
where deptno = 88;
select *
from empcon;
update empcon
set deptno = null
where deptno = 88;
update deptcon
set deptno = 44
where deptno = 88;
update empcon
set deptno = 44
where deptno is NULL;
select *
from empcon;
select *
from empcon
where deptno = 44;
drop table deptcon;
truncate table deptcon;
alter table deptcon
drop column deptno;
alter table empcon
drop constraint empcon_deptno_fk;
--on delete set null; 当主表中的一行数据被删除时，Oracle系统会自动地将所有从表中依赖于他的数据记录的外键改为空。
alter table empcon
add constraint empcon_deptno_fk
foreign key (deptno) references deptcon(deptno)
on delete set null;
select *
from deptcon
order by deptno;
select empno, ename, job, sal, deptno
from empcon
where deptno > 20
order by deptno;
delete from deptcon
where deptno = 44;
select * from deptcon;
select empno, ename, job, sal, deptno
from empcon
where deptno > 20
order by deptno;
select empno, ename, job, sal, deptno
from empcon
where deptno > 20
or deptno is null;
rollback;
alter table empcon
drop constraint empcon_deptno_fk;
--级联删除子表的数据
alter table empcon
add constraint empcon_deptno_fk
foreign key (deptno) references deptcon (deptno)
on delete cascade;
delete from deptcon
where deptno = 44;
select *
from deptcon;
select empno, ename, sal, job, deptno
from empcon;
--约束的维护
select owner, constraint_name,constraint_type, table_name,
r_constraint_name, status
from user_constraints
where table_name = 'EMPCON';
select owner, constraint_name,constraint_type, table_name,
r_constraint_name, status
from user_constraints
where table_name = 'DEPTCON';
alter table deptcon
disable constraint deptcon_deptcon_pk;
alter table deptcon
disable constraint deptcon_deptcon_pk casede;
alter table deptcon
enable constraint deptcon_deptcon_pk;
