oracle 5种约束条件使用方法

1 约束约束是操作表数据的强制规定有以下五种约束:•NOT NULL ;非空约束•UNIQUE ：唯一值约束•PRIMARY KEY：主键约束•FOREIGN KEY：外键约束•CHECK：检查约束约束：建表的时候可以加约束，建表后也可加约束注意事项、如果不指定约束名 Oracle server 自动按照 S、YS_C n 的格式指定约束名、在什么时候创建约束:•建表的同时•建表之后、可以在表级或列级定义约束、可以通过数据字典视图查看约束create table newdeptasselect * from departmentscreate table newempasselect * from employees2 定义约束CREATE TABLE [schema.]table(column datatype [DEFAULT expr][column_constraint],...[table_constraint][,...]);CREATE TABLE employees(employee_id NUMBER(6),first_name VARCHAR2(20),...job_id VARCHAR2(10) NOT NULL,CONSTRAINT emp_emp_id_pkP RIMARY KEY (EMPLOYEE_ID));列级column[CONSTRAINT constraint_name] 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 伪列•使用 SYSDATE, UID, USER, 和 USERENV 函数•在查询中涉及到其它列的值..., salary NUMBER(2)CONSTRAINT emp_salary_minCHECK (salary > 0),...限制工资不能少于2000alter table newempADD constraints newemp_salary_ck CHECK(salary>=2000)添加约束的语法使用 ALTER TABLE 语句:添加或删除约束, 但是不能修改约束有效化或无效化约束添加 NOT NULL 约束要使用 MODIFY 语句ALTER TABLE tableADD [CONSTRAINT constraint] type (column);添加约束ALTER TABLE employeesADD CONSTRAINT emp_manager_fkFOREIGN 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,2),hire_date DATECONSTRAINT emp_hire_date_nnNOT 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_ename_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,manager_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,...CONSTRAINT emp_email_uk UNIQUE(email));6 外键约束一般都是主键的ID 不能修改可以定义在表级或列级:FOREIGN KEY: 在表级指定子表中的列REFERENCES: 标示在父表中的列，后面跟一样的主键 ON DELETE CASCADE: 当父表中的列被删除是，子表中相对应的列也被删除ON DELETE SET NULL: 子表中相应的列置空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 ,...department_id NUMBER (4),CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)EMPLOYEESINSERT INTO允许 PRIMARYKEY ……REFERENCES departments(department_id),CONSTRAINT emp_email_uk UNIQUE(email));主外键关联外键约束:限制外键列值必须是引用主键中的主键列存在的列值，不限制空值ALter table newdeptadd constraint newdeptid primarykey(department_id)alter table newemp部门主键约束设置ADD CONSTRAINTS newemp_deptid_fk FOREIGNKEY(department_id)// newemp_deptid_fk鼠标点击名称references newdept (department_ID);//关联想要给newemp增加外键的前提是newdept必须有主键，先设置newdept的主键之后才会有外键7级联删除删除约束从表 EMPLOYEES 中删除约束ALTER TABLE employeesDROP CONSTRAINT emp_manager_fk;使用CASCADE选项删除约束外键和主键一起删除在ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。

使用 CASCADE 选项将相关的约束也无效化ALTER TABLE employeesDISABLE CONSTRAINT emp_emp_id_pk CASCADE;//中间的是约束的名字ALTER TABLE departmentsDROP PRIMARY KEY CASCADE;alter table newempadd constraints newemp_deptid_fk foreignkey(department_id)references newdept(department_id)//REFERENCES 关联的是父键dept所以是department_idon delete cascade级联置空员工不删除但是把外键删除alter table newempadd constraints newemp_deptid_fk foreignkey(department_id)references newdept(department_id)on delete set null//引用之前必须drop掉外键在一块执行ALTER TABLE newempADD CONSTRAINTS newemp_deptid_fk FOREIGNKEY(department_id)REFERENCES newdept(department_id)ON DELETE CASCADE;//必须删除外键之后才能执行语句ALTER TABLE newempADD CONSTRAINTS newemp_deptid_fk FOREIGNKEY(department_id)REFERENCES newdept(department_id)ON DELETE SET NULL; //想要删除不能按钮drop要用下面的语句删除外键alter table newempdrop CONSTRAINTS newemp_deptid_fk;8 激活约束在ALTER TABLE 语句中使用DISABLE 子句将约束无效化。

使用CASCADE 选项将相关的约束也无效化ENABLE 子句可将当前无效的约束激活ALTER TABLE employeesENABLE CONSTRAINT emp_emp_id_pk;Table altered.当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY索引无效化约束Alter table newempdisable constraint newemp_ename_pk激活约束Alter table newempenable constraint newemp_ename_pk9 查询约束查询数据字典视图USER_CONSTRAINTSSELECT constraint_name, constraint_type, search_conditionFROM user_constraintsWHERE table_name = 'EMPLOYEES';查询定义约束的列SELECT constraint_name, column_name FROM user_cons_columnsWHERE table_name = 'EMPLOYEES';10 建表的时候增加约束CREATE 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 key11 索引:主键唯一的，索引方便快速的查询，关联的机构，加快速度，目录索引方便地位查找，给小范围寻找索引还会往下查作用:加速查询，但是会降低DML操作一种数据库对象通过指针加速 Oracle 服务器的查询速度通过快速定位数据的方法，减少磁盘 I/O索引与表相互独立Oracle 服务器自动使用和维护索引创建索引、自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引、手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询索引分类B-tree 索引默认，适用于基数比较高，冗余数量较少的时候位图索引，适用于基数比较第，冗余数量较高的时候适合创建B-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 table new_empadd 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 查看索引的信息SELECT ic.index_name, ic.column_name, ic.column_position col_pos,ix.uniqueness FROM user_indexes ix, user_ind_columns ic WHERE ic.index_name = ix.index_nameAND ic.table_name = 'EMPLOYEES';select * from new_emp where employee_id= 174 select * from new_emp where last_name ='King' //King==king创建基于函数的索引create index newemp_lastname_idx onnew_emp(LOWER(last_name))select* from new_emp where LOWER(last_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 index删除索引UPPER_LAST_NAME_IDXDROP INDEX upper_last_name_idx;Index dropped.只有索引的拥有者或拥有DROP ANY INDEX权限的用户才可以删除索引drop index emp_lastname_idx //12 用户和权限权限数据库安全性:•系统安全性•数据安全性系统权限: 对于数据库的权限对象权限: 操作数据库对象的权限方案: 一组数据库对象集合, 例如表, 视图,和序列系统权限超过一百多种 100 有效的权限数据库管理员具有高级权限以完成管理任务，例如: •创建新用户•删除用户•删除表•备份表创建用户Cmd下SQLPLUSSystem /scott 小写Grant create user to SCOTTGrant create session to SCOTTRevoke create session to icss；在PL里面输入权限创建用户create user icss identified by icss create table a(id number(2))insert into a values(1)在DOS下输入Grant create session to icssgrant connect,resource to icss; alter user icss identified by123456接触锁定SqlplusSCOTT /TIGERConn system/scott;SCOTT SCOTT/TIGER;解锁Alter user SCOTT ACCOUNT UNLOCK; 权限Grant create session to xxx改密码Alter user 用户名identified by新密码。