索引

隐式创建（建表时）

显示创建（建表后独立）

创建表时加主键（约束）

字段后面主键默认约束名create table a1(id varchar2(50) primary key,name varchar(8));

字段后主键约束写法create table a3(id varchar2(50) constraint a3_id_pk primary key,name  varchar(8));

所有字段后主键约束写法create table a2(id varchar2(50),name varchar(8),constraint a2_id_pk primary key(id));

复合主键create table a2(id varchar2(50),name varchar(8),constraint a2_id_pk primary key(id,name));

在创建主键，会自动产生主键约束、主键唯一索引；

删除主键约束

删除主键约束alter table test_pri drop constraint pk_test_pri_a;

删除主键约束保留索引alter table test_pri drop constraint pk_test_pri_a keep index;

在删除主键时，会自动删除主键约束、主键唯一索引；

也可以删除主键用keep index 保留索引；

但是不可以删除有主键的索引

唯一索引（约束）

create table d13(d varchar2(50) constraint d13_uniq unique);

create unique index dept_id_2 on dept(department_id);

在创建唯一约束时会自动产生唯一索引；

删除唯一约束

SQL> select count(*) from user_constraints where TABLE_NAME='D13'; 1

SQL> select count(*) from user_indexes where TABLE_NAME='D13'; 1

删除唯一约束保留索引 alter table D13 drop constraint d13_uniq keep index;

SQL> select count(*) from user_constraints where TABLE_NAME='D13'; 0

SQL> select count(*) from user_indexes where TABLE_NAME='D13'; 1

未删除索引之前还是不可以重复

SQL> select TABLE_NAME,index_name from user_indexes where table_name='D13';

D13 D13_UNIQ

QL> insert into D13 values('a');

1 row created.

SQL> /       

insert into D13 values('a')

*ERROR at line 1:ORA-00001: unique constraint (HR.D13_UNIQ) violated

SQL> drop index D13_UNIQ;

Index dropped.

SQL> insert into D13 values('a');

1 row created.

那unique index比unique 列更好

在删除唯一约束时，会自动删除唯一约束、唯一索引；

也可以删除唯一约束用keep index 保留索引；

但是不可以删除有唯一约束的索引

而且只要有唯一索引在，在删除了约束下也不可以重复

创建表时加外键（约束）

Create table emp as select * from employees

alter table emp modify EMPNO number(4) unique;

Alter table dept add constraint dept_pk primary key (DEPARTMENT_ID);

Alter table dept add constraint dept_un unique (DEPARTMENT_ID);

外键create table emp2(id number references emp(EMPNO),name varchar(8));

组合外键create table emp0(id number ,name varchar(8) ,constraint fk_nam1e foreign key(id,name) references emp9(id,name));

外键指向其它表的unique或者pk列，但不是unique索引列。

创建表之后加外键（约束）

Alter table employees add constraint emp_fk_deptid foreign key (DEPARTMENT_ID) references dept(DEPARTMENT_ID) on delete cascade;

如果有on delete cascade则删除dept时有关联的子表行也会删除。

如果有on delete set null则删除dept时有关联的子表行列变空

如果有on delete no action则删除dept时有关联的子表行不变化

创建B*树普通索引

普通索引Create index cust_name_i on customers(customer_name,customer_status);

联合索引create index idx_dept_name_no on dept(dname,deptno);

建立表时加约束

可以在字段名后加，

也可以集中在语句后

Not null

Unique

Primary key

Foreign key

check

Create table student132(

sid number(8,0),

name varchar2(20),

sex char(2) constraint nn_sex12 not null,

sex2 char(2) check(sex2='aa' or sex2='bb'),

email varchar2(50)   unique,

email2 varchar2(50),

class varchar2(30),

Constraint namec check(name is not null),

constraint uk_email2 unique(email2),

constraint pk_sid12 primary key(sid),

constraint fk_nam1e foreign key(id,name) references emp9(id,name)),

constraint ck_sex check (sex=' 男 ' or sex=' 女 ')

);

table can have only one primary key

一个表只能有一个主键索引必须,

一个表可以有多个unique。

主键必须约束有名，而其它约束可以用默认。

约束名称在同一模式用户下不能重名。

一个约束名字

后期加上列上的约束

汉字的问题

alter table ab3 add constraint abc3_nullnot check(a is not null);

alter table student add constraint ck_sex check (sex='男' or sex='女')

drop  table student12;

create table student12(sex2 char(2) check(sex2='a' or sex2='b'));

drop  table student12;

create table student12(sex2 char(2) check(sex2='男' or sex2='女'));

Sqlplus 必须在汉字前后带空格，为什么呢？？否则会报错。。。

create table student12(sex2 char(2) check(sex2=' 男 ' or sex2=' 女 '));

SQL> insert into ab3 values(' 男 ');

ERROR at line 1:

ORA-12899: value too large for column "HR"."AB3"."A" (actual: 7, maximum: 4)

SQL> alter table ab3 modify a varchar2(7);

Table altered.

SQL> insert into ab3 values(' 男 ');

1 row created.

而且这样后一个长度变成了7，也就是1234567长度，乱了

创建位图索引

Create bitmap index emp_index_i on employees(sex);

B树索引，主要用于连续相互关系

位图索引用于列值比较少，比如男女等，select 时过滤会比较快

Drop 删除 B和位图索引

Drop index cust_name_i

Drop index emp_index_i 

不同类型的index,一样的drop

索引不能alter只能drop

Alter 禁止、启用、删除  约束

每个没有给名字约束都会有默认名

alter table student add/disable/enable/drop  constraint 约束名

Alter 禁止、启用、删除  约束

默认约束状态是开启，但现实中约束如果是后期加上，可能里面已有数据，不一定符合怎么办

ENABLE VALIDATE        启用及有效，与ENABLE同样作用

DISABLE NOVALIDATE  停用及允许无效，与DISABLE同样作用，不因部分影响整体导入

ENABLE NOVALIDATE   启用及允许无效，批量上传完后，又开启约束

DISABLE VALIDATE       停用及有效

如果达不到条件，就不可以切换到disable validate/enable validate状态下。

SQL> create table ed(ab varchar2(10));

Table created.

SQL> alter table ed add constraint ab_con check(ab is not null);

Table altered.

SQL> insert into ed('');

insert into ed('')

               *

ERROR at line 1:

ORA-00928: missing SELECT keyword

停用及允许无效

SQL> alter table ed disable NOVALIDATE constraints ab_con;   /alter table ed modify constraint ab_con disable novalidate;

同等作用

SQL> alter table ed disable constraints ab_con;/alter table ed modify constraints ab_con disable;

Table altered.

SQL> insert into ed values('');

1 row created.

重新约束

SQL>alter table ed enable NOVALIDATE constraints ab_con;/alter table ed modify constraint ab_con enable novalidate;

有了不符合的数据后就不能再用enable validate或disable validate了，也就是状态改变前可以校验内容情况

SQL> alter table ed enable  constraints ab_con;/ alter table ed enable  validate constraints ab_con

SQL> alter table ed disable validate constraints ab_con;/alter table ed modify constraints ab_con disable validate;

SQL>alter table ed drop  constraint ab_con;

查询索引和约束

select TABLE_NAME,INDEX_NAME from user_indexes where TABLE_NAME='D13';

select TABLE_NAME  from user_constraints where TABLE_NAME='D13';

如何去处理被外键作为参照的主键

SQL> drop table dept;

drop table dept

           *

ERROR at line 1:

ORA-02449: unique/primary keys in table referenced by foreign keys

SQL>

select c.TABLE_NAME tablename,c.constraint_name foreignname,u.column_name columnname from all_constraints p, all_constraints c,user_cons_columns u where p.table_name = 'DEPT' and p.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') and c.OWNER=SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') and c.constraint_type = 'R' and c.CONSTRAINT_NAME=u.constraint_name and p.CONSTRAINT_NAME = c.R_CONSTRAINT_NAME

TABLENAME  FOREIGNNAME  COLUMNNAME

EMPLOYEES  EMP_FK_222  DEPARTMENT_ID

alter table employees drop constraint EMP_FK_222;不影响子表的department_id内容