1、Including Constraints,Objectives,After completing this lesson, you should be able to do the following:Describe constraintsCreate and maintain constraints,What are Constraints?,Constraints enforce rules at the table level.Constraints prevent the deletion of a table if there are dependencies.The follo
2、wing constraint types are valid:NOT NULLUNIQUE PRIMARY KEYFOREIGN KEYCHECK,Constraint Guidelines,Name a constraint or the Oracle server generates a name by using the SYS_Cn format.Create a constraint either:At the same time as the table is created, orAfter the table has been createdDefine a constrai
3、nt at the column or table level.View a constraint in the data dictionary.,Defining Constraints,Defining Constraints,Column constraint levelTable constraint level,column CONSTRAINT constraint_name constraint_type,column,. CONSTRAINT constraint_name constraint_type (column, .),The NOT NULL Constraint,
4、Ensures that null values are not permitted for the column:,CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,.,The NOT NULL Constraint,Is defined at the column level:,The
5、 UNIQUE Constraint,EMPLOYEES,UNIQUE constraint,INSERT INTO,The UNIQUE Constraint,Defined at either the table level or the column level:,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 N
6、OT NULL,. CONSTRAINT emp_email_uk UNIQUE(email);,The PRIMARY KEY Constraint,DEPARTMENTS,PRIMARY KEY,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(departm
7、ent_id);,The PRIMARY KEY Constraint,Defined at either the table level or the column level:,The FOREIGN KEY Constraint,DEPARTMENTS,EMPLOYEES,The FOREIGN KEY Constraint,Defined at either the table level or the column level:,CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL
8、, 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);,FOREIGN KEY Constraint Keywords,FOREIGN KEY: Defin
9、es the column in the child table at the table constraint levelREFERENCES: Identifies the table and column in the parent tableON DELETE CASCADE: Deletes the dependent rows in the child table when a row in the parent table is deleted.ON DELETE SET NULL: Converts dependent foreign key values to null,Th
10、e CHECK Constraint,Defines a condition that each row must satisfyThe following expressions are not allowed:References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functionsQueries that refer to other values in other rows,., salaryNUMBER(2) CONSTRAINT
11、emp_salary_min CHECK (salary 0),.,Adding a Constraint Syntax,Use the ALTER TABLE statement to:Add or drop a constraint, but not modify its structureEnable or disable constraintsAdd a NOT NULL constraint by using the MODIFY clause,ALTER TABLE table ADD CONSTRAINT constraint type (column);,Adding a Co
12、nstraint,Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already exist as a valid employee in the EMPLOYEES table.,ALTER TABLE employeesADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);Table altered.,Dropping a Constraint,Remo
13、ve the manager constraint from the EMPLOYEES table.Remove the PRIMARY KEY constraint on the DEPARTMENTS table and drop the associated FOREIGN KEY constraint on the EMPLOYEES.DEPARTMENT_ID column.,ALTER TABLE employeesDROP CONSTRAINT emp_manager_fk;Table altered.,ALTER TABLEdepartmentsDROP PRIMARY KE
14、Y CASCADE;Table altered.,Disabling Constraints,Execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint.Apply the CASCADE option to disable dependent integrity constraints.,ALTER TABLEemployeesDISABLE CONSTRAINTemp_emp_id_pk CASCADE;Table altered.,Enabling Constr
15、aints,Activate an integrity constraint currently disabled in the table definition by using the ENABLE clause. A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARY KEY constraint.,ALTER TABLEemployeesENABLE CONSTRAINTemp_emp_id_pk;Table altered.,Cascading Const
16、raints,The CASCADE CONSTRAINTS clause is used along with the DROP COLUMN clause.The CASCADE CONSTRAINTS clause drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns.The CASCADE CONSTRAINTS clause also drops all multicolumn constraints de
17、fined on the dropped columns.,Cascading Constraints,Example:,ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;Table altered.,ALTER TABLE test1 DROP (pk, fk, col1) CASCADE CONSTRAINTS;Table altered.,SELECTconstraint_name, constraint_type,search_conditionFROMuser_constraintsWHEREtable_name = EMPLOYEES;
18、,Viewing Constraints,Query the USER_CONSTRAINTS table to view all constraint definitions and names.,SELECTconstraint_name, column_nameFROMuser_cons_columnsWHEREtable_name = EMPLOYEES;,Viewing the Columns Associated with Constraints,View the columns associated with the constraint names in the USER_CO
19、NS_COLUMNS view.,Summary,In this lesson, you should have learned how to create constraints.Types of constraints:NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECKYou can query the USER_CONSTRAINTS table to view all constraint definitions and names.,Practice 10 Overview,This practice covers the following topics:Adding constraints to existing tablesAdding more columns to a tableDisplaying information in data dictionary views,
