constraint&index

索引 隐式创建（建表时） 显示创建（建表后独立）
创建表时加主键（约束）	字段后面主键默认约束名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;这样后才能去删除dept	不影响子表的department_id内容     SQL> create table inac (n number); Table created. SQL> create index inac on inac(n); Index created. 说明索引的名称空间有自己独立的，可以和表同名。