What Are Constraints Constraints enforce rules at the

  • Slides: 10
Download presentation
What Are Constraints? • Constraints enforce rules at the table level. • Constraints prevent

What Are Constraints? • Constraints enforce rules at the table level. • Constraints prevent the deletion of a table if there are dependencies. • The following constraint types are valid in Oracle: – NOT NULL – UNIQUE – PRIMARY KEY – FOREIGN KEY – CHECK 11 -1 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Constraint Guidelines • Name a constraint or the Oracle Server will generate a name

Constraint Guidelines • Name a constraint or the Oracle Server will generate a name by using the SYS_Cn format. • Create a constraint: – At the same time as the table is created – After the table has been created • Define a constraint at the column or table level. • View a constraint in the data dictionary. 11 -2 Copyright Ó Oracle Corporation, 1998. All rights reserved.

The NOT NULL Constraint Defined at the column level SQL> CREATE TABLE 2 empno

The NOT NULL Constraint Defined at the column level SQL> CREATE TABLE 2 empno 3 ename 4 job 5 mgr 6 hiredate 7 sal 8 comm 9 deptno 11 -3 emp( NUMBER(4), VARCHAR 2(10) NOT NULL, VARCHAR 2(9), NUMBER(4), DATE, NUMBER(7, 2), NUMBER(7, 2) NOT NULL); Copyright Ó Oracle Corporation, 1998. All rights reserved.

The PRIMARY KEY Constraint Defined at either the table level or the column level

The PRIMARY KEY Constraint Defined at either the table level or the column level SQL> CREATE TABLE 2 deptno 3 dname 4 loc 5 CONSTRAINT 6 CONSTRAINT 11 -4 dept( NUMBER(2), VARCHAR 2(14), VARCHAR 2(13), dept_dname_uk UNIQUE (dname), dept_deptno_pk PRIMARY KEY(deptno)); Copyright Ó Oracle Corporation, 1998. All rights reserved.

The FOREIGN KEY Constraint Defined at either the table level or the column level

The FOREIGN KEY Constraint Defined at either the table level or the column level SQL> CREATE TABLE emp( 2 empno NUMBER(4), 3 ename VARCHAR 2(10) NOT NULL, 4 job VARCHAR 2(9), 5 mgr NUMBER(4), 6 hiredate DATE, 7 sal NUMBER(7, 2), 8 comm NUMBER(7, 2), 9 deptno NUMBER(7, 2) NOT NULL, 10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) 11 REFERENCES dept (deptno)); 11 -5 Copyright Ó Oracle Corporation, 1998. All rights reserved.

The CHECK Constraint • Defines a condition that each row must satisfy • Expressions

The CHECK Constraint • Defines a condition that each row must satisfy • Expressions that are not allowed: – References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns – Calls to SYSDATE, UID, USER, and USERENV functions – Queries that refer to other values in other rows. . . , deptno NUMBER(2), CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99), . . . 11 -6 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Adding a Constraint Add a FOREIGN KEY constraint to the EMP table indicating that

Adding a Constraint Add a FOREIGN KEY constraint to the EMP table indicating that a manager must already exist as a valid employee in the EMP table. SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr) REFERENCES emp(empno); Table altered. 11 -7 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Dropping a Constraint • Remove the manager constraint from the EMP table. SQL> ALTER

Dropping a Constraint • Remove the manager constraint from the EMP table. SQL> ALTER TABLE 2 DROP CONSTRAINT Table altered. emp_mgr_fk; • Remove the PRIMARY KEY constraint on the DEPT table and drop the associated FOREIGN KEY constraint on the EMP. DEPTNO column. SQL> ALTER TABLE dept 2 DROP PRIMARY KEY CASCADE; Table altered. 11 -8 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Disabling Constraints • Execute the DISABLE clause of the ALTER TABLE statement to deactivate

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. SQL> ALTER TABLE 2 DISABLE CONSTRAINT Table altered. 11 -9 emp_empno_pk CASCADE; Copyright Ó Oracle Corporation, 1998. All rights reserved.

Enabling Constraints • Activate an integrity constraint currently disabled in the table definition by

Enabling Constraints • Activate an integrity constraint currently disabled in the table definition by using the ENABLE clause. SQL> ALTER TABLE 2 ENABLE CONSTRAINT Table altered. emp_empno_pk; • A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARY KEY constraint. 11 -10 Copyright Ó Oracle Corporation, 1998. All rights reserved.

Lecture 03 Constraints Example Schema CONSTRAINTS Constraints ConstraintsLecture 03 Constraints Example Schema CONSTRAINTS Constraints Constraints
Social Constraints Breaking social constraints Carnival Social ConstraintsSocial Constraints Breaking social constraints Carnival Social Constraints
Constraints Constraints in Inventor Constraints Defined Constraint TheConstraints Constraints in Inventor Constraints Defined Constraint The
Constraints on Organisations Statutory constraints Statutory constraints areConstraints on Organisations Statutory constraints Statutory constraints are
Species Constraints Unconstrained Learning Timing Constraints Domain ConstraintsSpecies Constraints Unconstrained Learning Timing Constraints Domain Constraints
Database Constraints Database Constraints Database constraints are restrictionsDatabase Constraints Database Constraints Database constraints are restrictions
Semantic analysis l l Enforce contextdependent language rulesSemantic analysis l l Enforce contextdependent language rules
Government and Economy Make and enforce rules RegulateGovernment and Economy Make and enforce rules Regulate
Integrity Constraints Integrity constraints are rules enforced whenIntegrity Constraints Integrity constraints are rules enforced when