资源描述
Click to edit Master title style,First Level,Second Level,Third Level,Fourth Level,Fifth Level,10-,*,Copyright Oracle Corporation,2001.All rights reserved.,约束,目标,通过本章学习，,您,您将可以:,描述约束,创建和维护约束,什么是约束,约束是表级的强,制,制规定,约束放置在表中,删,删除有关联关系,的,的数据,有以下五种约束,:,NOT NULL,UNIQUE,PRIMARYKEY,FOREIGNKEY,CHECK,注意事项,如果不指定约束,名,名,Oracleserver,自动按照,SYS_C,n,的格式指定约束,名,名,在什么时候创建,约,约束,:,建表的同时,建表之后,可以在表级或列,级,级定义约束,可以通过数据字,典,典视图查看约束,定义约束,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_pk,PRIMARY KEY(EMPLOYEE_ID);,定义约束,列级,表级,column,CONSTRAINT,constraint_name,constraint_type,column,.,CONSTRAINT,constraint_name,constraint_type,(,column,.),NOTNULL,约束,保证列值,不,不能为空,:,NOT NULL,约束,无,NOT NULL,约束,NOT NULL,约束,CREATE TABLEemployees(,employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,salaryNUMBER(8,2),commission_pct NUMBER(2,2),hire_dateDATE,CONSTRAINT emp_hire_date_nn,NOTNULL,.,NOTNULL,约束,只能定义,在,在列级,:,系统命名,用户命名,UNIQUE,约束,EMPLOYEES,UNIQUE,约束,INSERT INTO,不允许,:,已经存在,允许,UNIQUE,约束,可以定义,在,在表级或,列,列级,:,CREATE TABLEemployees(,employee_idNUMBER(6),last_nameVARCHAR2(25)NOT NULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATE NOT NULL,.,CONSTRAINT emp_email_ukUNIQUE(email);,PRIMARYKEY,约束,DEPARTMENTS,PRIMARYKEY,INSERT INTO,不允许,(,空值,),不允许,(50,已经存在,),CREATE TABLEdepartments(,department_idNUMBER(4),department_nameVARCHAR2(30),CONSTRAINT dept_name_nnNOTNULL,manager_idNUMBER(6),location_idNUMBER(4),CONSTRAINT dept_id_pk PRIMARY KEY(department_id);,PRIMARYKEY,约束,可以定义,在,在表级或,列,列级,:,FOREIGNKEY,约束,DEPARTMENTS,EMPLOYEES,FOREIGNKEY,INSERT INTO,不允许,(,9,不存在,),允许,PRIMARYKEY,FOREIGNKEY,约束,可以定义,在,在表级或,列,列级,:,CREATE TABLEemployees(,employee_idNUMBER(6),last_nameVARCHAR2(25)NOT NULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATE NOT NULL,.,department_idNUMBER(4),CONSTRAINT emp_dept_fk FOREIGN KEY(department_id),REFERENCES departments(department_id),CONSTRAINT emp_email_ukUNIQUE(email);,FOREIGNKEY,约束的关,键,键字,FOREIGNKEY,:,在表级指,定,定子表中,的,的列,REFERENCES,:,标示在父,表,表中的列,ON DELETE CASCADE,:,当父表中,的,的列被删,除,除是，子,表,表中相对,应,应的列也,被,被删除,ON DELETE SET NULL,:,子表中相,应,应的列置,空,空,CHECK,约束,定义每一,行,行必须满,足,足的条件,以下的表,达,达式是不,允,允许的,:,出现,CURRVAL,NEXTVAL,LEVEL,和,ROWNUM,伪列,使用,SYSDATE,UID,USER,和,USERENV,函数,在查询中,涉,涉及到其,它,它列的值,.,salaryNUMBER(2),CONSTRAINT emp_salary_min,CHECK(salary 0),.,添加约束,的,的语法,使用,ALTER TABLE,语句,:,添加或删,除,除约束,但是不能,修,修改约束,有效化或,无,无效化约,束,束,添加,NOTNULL,约束要使,用,用,MODIFY,语句,ALTER TABLE,table,ADDCONSTRAINT,constraint,type,(,column,);,添加约束,添加约束,举,举例,ALTER TABLEemployees,ADDCONSTRAINTemp_manager_fk,FOREIGNKEY(manager_id),REFERENCES employees(employee_id);,Table altered.,删除约束,从表,EMPLOYEES,中删除约,束,束,使用,CASCADE,选项删除,约,约束,ALTER TABLEemployees,DROPCONSTRAINTemp_manager_fk;,Table altered.,ALTER TABLEdepartments,DROPPRIMARYKEYCASCADE;,Table altered.,无效化约,束,束,在,ALTERTABLE,语句中使,用,用,DISABLE,子句将约,束,束无效化,。,。,使用,CASCADE,选项将相,关,关的约束,也,也无效化,ALTERTABLEemployees,DISABLE CONSTRAINTemp_emp_id_pk CASCADE;,Tablealtered.,激活约束,ENABLE,子句可将当前,无,无效的约束激,活,活,当定义或激活,UNIQUE,或,PRIMARY KEY,约束时系统会,自,自动创建,UNIQUE,或,PRIMARY KEY,索引,ALTERTABLEemployees,ENABLECONSTRAINTemp_emp_id_pk;,Tablealtered.,及连约束,CASCADE CONSTRAINTS,子句在,DROP COLUMN,子句中使用,在删除表的列,时,时,CASCADE CONSTRAINTS,子句指定将相,关,关的约束一起,删,删除,在删除表的列,时,时,CASCADE CONSTRAINTS,子句同时也删,除,除多列约束,及连约束,及连约束举例,:,ALTERTABLEtest1,DROP(pk)CASCADECONSTRAINTS;,Tablealtered.,ALTERTABLEtest1,DROP(pk,fk,col1)CASCADE CONSTRAINTS;,Tablealtered.,SELECTconstraint_name,constraint_type,search_condition,FROMuser_constraints,WHEREtable_name=EMPLOYEES;,查询约束,查询数据字典,视,视图,USER_CONSTRAINTS,SELECTconstraint_name,column_name,FROMuser_cons_columns,WHEREtable_name=EMPLOYEES;,查询定义约束,的,的列,查询数据字典,视,视图,USER_CONS_COLUMNS,总结,通过本章学习,，,，您已经学会,如,如何创建约束,描述约束的类,型,型,:,NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,查询数据字典,视,视图,以获得约束的,信,信息,
展开阅读全文