oracle 5种约束条件使用方法

1、1 约束约束是操作表数据的强制规定有以下五种约束: NOT NULL ;非空约束 UNIQUE ：唯一值约束 PRIMARY KEY：主键约束 FOREIGN KEY：外键约束 CHECK：检查约束约束：建表的时候可以加约束，建表后也可加约束注意事项、如果不指定约束名 Oracle server 自动按照 S、YS_Cn 的格式指定约束名、在什么时候创建约束: 建表的同时 建表之后、可以在表级或列级定义约束、可以通过数据字典视图查看约束create table newdeptas select * from departmentscreate table newempas select * f

2、rom employees2 定义约束CREATE TABLE schema.table (column datatype DEFAULT exprcolumn_constraint,.table_constraint,.);CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), . job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID);列级column CONSTRAINT constraint_n

3、ame constraint_type, 表级column,. CONSTRAINT constraint_name constraint_type (column, .),3 NOT NULL 约束只能定义在列级:保证列值不能为空:修改first_name 非空alter table newempmodify (first_name NOT NULL)取消非空约束alter table newempmodify (first_name NULL)增加约束定义每一行必须满足的条件以下的表达式是不允许的: 出现CURRVAL, NEXTVAL, LEVEL, 和ROWNUM 伪列 使用 SYSD

4、ATE, UID, USER, 和 USERENV 函数 在查询中涉及到其它列的值., salaryNUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0),.限制工资不能少于2000alter table newempADD constraints newemp_salary_ck CHECK(salary>=2000)添加约束的语法使用 ALTER TABLE 语句:添加或删除约束, 但是不能修改约束有效化或无效化约束添加 NOT NULL 约束要使用 MODIFY 语句 ALTER TABLE tableADD CONSTR

5、AINT constraint type (column);添加约束 ALTER TABLE employeesADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);alter table 表名modify （自定义列名 约束设置）CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2

6、,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,4 主键约束限制列值不允许空值，且不能为空，一个表只有一个主键只要主键都会Alter，一般主键是ID唯一的alter table newempADD constraints newemp_empid_pk primary key(employee_id)删除主键约束alter table newempdrop constraints newemp_empid_pk;复合主键 （多个列设置为主键）ALter table newempadd constraint newemp_enam

7、e_pk primary key(first_name, last_name)5 唯一约束唯一值约束，限制列值不允许重复，可以为空（不会限制空值），一个表可以有多个唯一值的约束，不允许insert into可以定义在表级或列级:设置唯一值约束alter table newempADD constraints newemp_email_uk UNIQUE(email) CREATE TABLE departments( department_id NUMBER(4), department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, m

8、anager_id NUMBER(6), location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id);查约束，限制自定义条件，忽略空值可以定义在表级或列级: CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25) , salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,. CONS

9、TRAINT emp_email_uk UNIQUE(email);6 外键约束一般都是主键的ID 不能修改EMPLOYEESINSERT INTO允许PRIMARYKEY可以定义在表级或列级:FOREIGN KEY: 在表级指定子表中的列REFERENCES: 标示在父表中的列，后面跟一样的主键ON DELETE CASCADE: 当父表中的列被删除是，子表中相对应的列也被删除ON DELETE SET NULL: 子表中相应的列置空CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL,

10、email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,. department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email);主外键关联外键约束:限制外键列值必须是引用主键中的主键列存在的列值，不限制空值ALter table ne

11、wdeptadd constraint newdeptid primary key(department_id) alter table newemp部门主键约束设置ADD CONSTRAINTS newemp_deptid_fk FOREIGN KEY(department_id)/ newemp_deptid_fk鼠标点击名称references newdept (department_ID);/关联想要给newemp增加外键的前提是newdept必须有主键，先设置newdept的主键之后才会有外键7级联删除删除约束从表 EMPLOYEES 中删除约束 ALTER TABLE employ

12、eesDROP CONSTRAINT emp_manager_fk;使用CASCADE选项删除约束外键和主键一起删除在ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。使用 CASCADE 选项将相关的约束也无效化ALTER TABLEemployeesDISABLE CONSTRAINTemp_emp_id_pk CASCADE;/中间的是约束的名字ALTER TABLEdepartmentsDROP PRIMARY KEY CASCADE;alter table newempadd constraints newemp_deptid_fk foreign key(dep

13、artment_id)references newdept(department_id)/REFERENCES 关联的是父键dept所以是department_idon delete cascade级联置空员工不删除但是把外键删除alter table newempadd constraints newemp_deptid_fk foreign key(department_id)references newdept(department_id)on delete set null/引用之前必须drop掉外键在一块执行ALTER TABLE newemp ADD CONSTRAINTS new

14、emp_deptid_fk FOREIGN KEY(department_id) REFERENCES newdept(department_id) ON DELETE CASCADE;/必须删除外键之后才能执行语句ALTER TABLE newemp ADD CONSTRAINTS newemp_deptid_fk FOREIGN KEY(department_id) REFERENCES newdept(department_id) ON DELETE SET NULL; /想要删除不能按钮drop要用下面的语句删除外键 alter table newemp drop CONSTRAINT

15、S newemp_deptid_fk;8 激活约束在ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。使用 CASCADE 选项将相关的约束也无效化ENABLE 子句可将当前无效的约束激活 ALTER TABLEemployeesENABLE CONSTRAINTemp_emp_id_pk;Table altered.当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY索引无效化约束Alter table newempdisable constraint newemp_ename_pk激活约束Alter tabl

16、e newempenable constraint newemp_ename_pk9 查询约束查询数据字典视图 USER_CONSTRAINTSSELECTconstraint_name, constraint_type,search_conditionFROMuser_constraintsWHEREtable_name = 'EMPLOYEES'查询定义约束的列SELECTconstraint_name, column_nameFROMuser_cons_columnsWHEREtable_name = 'EMPLOYEES'10 建表的时候增加约束CREA

17、TE table emp2(emp_id number(10) primary key, 设置主键emp_name varchar2(60) NOT NULL, 非空约束emp_email varchar2(50) UNIQUE, 唯一值约束emp_sal number(8,2) check(emp_sal>=500),检查约束emp_deptid number(6) references newdept (department_id) 外键约束constraints emp2_emp2_uk unique(emp_email) /,没有完成要加， 强制约束条件同primary key1

18、1 索引:主键唯一的 ，索引方便快速的查询，关联的机构，加快速度，目录索引方便地位查找，给小范围寻找索引还会往下查作用:加速查询，但是会降低DML操作一种数据库对象通过指针加速 Oracle 服务器的查询速度通过快速定位数据的方法，减少磁盘 I/O索引与表相互独立Oracle 服务器自动使用和维护索引创建索引、自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引、手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询索引分类B-tree 索引 默认，适用于基数比较高，冗余数量较少的时候位图索引，适用于基数比较第，冗余数量较高的时候适合创建B

19、-TREE索引的时机：1.列值基数比较高（重复率比较低）2.不以空值做条件3.列经常出现在条件子句中4.表很大5.经常做查询操作，而不是DML操作6.满足条件的数据不超过总记录数的15%7.模糊查询不走索引，如果模糊查询想使用索引，必须用全文检索技术创建索引在一个或多个列上创建索引CREATE INDEX indexON table (column, column.);在表 EMPLOYEES的列 LAST_NAME 上创建索引CREATE INDEX emp_last_name_idxON employees(last_name);Index created.创建唯一索引alter tabl

20、e new_emp add constraints newemp_empid_pk primary key(employee_id)select * from new_emp创建普通索引create index emp_lastname_idx on new_EMP(last_name)查询索引可以使用数据字典视图USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息SELECTic.index_name, ic.column_name,ic.column_position col_pos,ix.uniquenessFROMuser_indexes ix, user_i

21、nd_columns icWHEREic.index_name = ix.index_nameANDic.table_name = 'EMPLOYEES'select * from new_emp where employee_id= 174select * from new_emp where last_name ='King'/King=king创建基于函数的索引create index newemp_lastname_idx on new_emp(LOWER(last_name)select * from new_emp where LOWER(last_

22、name)='King'基于函数的索引基于函数的索引是一个基于表达式的索引索引表达式由列, 常量, SQL 函数和用户自定义的函数CREATE INDEX upper_dept_name_idxON departments(UPPER(department_name);Index created.SELECT *FROM departmentsWHERE UPPER(department_name) = 'SALES'位图索引位图索引是在基数较小，即重复率很大的列值中创建比较适用 create bitmap index emp_jobid_bmidxon empcopy (job_id);create index newemp_jobid_idx on new_emp(job_id)select * from new_emp where job_id='IT_PROG'RBO和CBORBO ： 基于规则的优化方式(Rule-Based Optimization ）CBO : 基于代价的优化方式(Cost-Based Optimization )删除索引使用DROP INDEX 命令删除索引DROP INDEX ind