Referential integrity constraints must be enforced by the application program.

This chapter applies to only to constraints on tables. Constraints on views do not help maintain data integrity or have associated indexes. Instead, they enable query rewrites on queries involving views, thereby improving performance when using materialized views and other data warehousing features.

See Also:

• Oracle Database Concepts for information about data integrity and constraints

• Oracle Database Administrator's Guide for more information about managing constraints

• Oracle Database SQL Language Reference for the syntactic and semantic information about constraints

• Oracle Database SQL Language Reference for more information about constraints on views

• Oracle Database Data Warehousing Guide for information about using constraints in data warehouses

• for more information about the role that data type plays in data integrity

Topics:

12.1 Enforcing Business Rules with Constraints

Whenever possible, enforce business rules with constraints. Constraints have the advantage of speed: Oracle Database can check that all the data in a table obeys a constraint faster than application code can do the equivalent checking.

creates a table of departments, a table of employees, a constraint to enforce the rule that all values in the department table are unique, and a constraint to enforce the rule that every employee must work for a valid department.

Example 12-1 Enforcing Business Rules with Constraints

Create table of departments:

DROP TABLE dept_tab;
CREATE TABLE dept_tab (
  deptname VARCHAR2(20),
  deptno   INTEGER
);
 

Create table of employees:

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Create constraint to enforce rule that all values in department table are unique:

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Create constraint to enforce rule that every employee must work for a valid department:

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Now, whenever you insert an employee record into

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

Suppose that instead of using a constraint to enforce the rule that every employee must work for a valid department, you use a trigger that queries

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

See Also:

• Oracle Database SQL Language Reference for syntactic and semantic information about constraints

• Oracle Database Concepts for the complete list of advantages of integrity constraints

• Oracle Database Concepts for more information about using triggers to enforce business rules

12.2 Enforcing Business Rules with Both Constraints and Application Code

Enforcing business rules with both constraints and application code is recommended when application code can determine that data values are invalid without querying tables. The application code can provide immediate feedback to the user and reduce the load on the database by preventing attempts to insert invalid data into tables.

For , assume that was run and then this column was added to the table

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

empgender VARCHAR2(1)

The only valid values for

Gender must be M or F.

Gender must be M or F.

Gender must be M or F.

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

Gender must be M or F.

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

Gender must be M or F.

Example 12-2 Enforcing Business Rules with Both Constraints and Application Code

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

Result:

Gender must be M or F.

12.3 Creating Indexes for Use with Constraints

When a unique or primary key constraint is enabled, Oracle Database creates an index automatically, but Oracle recommends that you create these indexes explicitly. If you want to use an index with a foreign key constraint, then you must create the index explicitly.

When a constraint can use an existing index, Oracle Database does not create an index for that constraint. Note that:

• A unique or primary key constraint can use either a unique index, an entire nonunique index, or the first few columns of a nonunique index.

• If a unique or primary key constraint uses a nonunique index, then no other unique or primary key constraint can use that nonunique index.

• The column order in the constraint and index need not match.

• The object number of the index used by a unique or primary key constraint is stored in

  Gender must be M or F.

  9.

  DESCRIBE DEPARTMENTS;
  

  0 for that constraint. No static data dictionary view or dynamic performance view shows this information.

  If an enabled unique or primary key constraint is using an index, you cannot drop only the index. To drop the index, you must either drop the constraint itself or disable the constraint and then drop the index.

See Also:

• Oracle Database Administrator's Guide for more information about indexes associated with constraints

• Oracle Database Administrator's Guide for information about disabling and dropping constraints

• Oracle Database Administrator's Guide for information about creating indexes explicitly

• Oracle Database SQL Language Reference for information about creating indexes explicitly

12.4 When to Use NOT NULL Constraints

By default, a column can contain a

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

Use a

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

• A column must contain a non-

  DESCRIBE DEPARTMENTS;
  

  1 value.

  For example, in the table

  DESCRIBE DEPARTMENTS;
  

  8.

  CREATE OR REPLACE PROCEDURE add_employee (
    e_name   emp_tab.empname%TYPE,
    e_gender emp_tab.empgender%TYPE,
    e_number emp_tab.empno%TYPE,
    e_dept   emp_tab.deptno%TYPE
  ) AUTHID DEFINER IS
  BEGIN
    IF UPPER(e_gender) IN ('M','F') THEN
      INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
    END IF;
  END;
  /
   
  BEGIN
    add_employee ('Smith', 'H', 356, 20);
  END;
  /
  

  2, each employee must have an employee ID. Therefore, the column

  DESCRIBE DEPARTMENTS;
  

  8.

  CREATE OR REPLACE PROCEDURE add_employee (
    e_name   emp_tab.empname%TYPE,
    e_gender emp_tab.empgender%TYPE,
    e_number emp_tab.empno%TYPE,
    e_dept   emp_tab.deptno%TYPE
  ) AUTHID DEFINER IS
  BEGIN
    IF UPPER(e_gender) IN ('M','F') THEN
      INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
    END IF;
  END;
  /
   
  BEGIN
    add_employee ('Smith', 'H', 356, 20);
  END;
  /
  

  2.

   Name                                      Null?    Type
   ----------------------------------------- -------- ------------
   
   DEPARTMENT_ID                             NOT NULL NUMBER(4)
   DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
   MANAGER_ID                                         NUMBER(6)
   LOCATION_ID                                        NUMBER(4)
  

  2 has a

  DESCRIBE DEPARTMENTS;
  

  3

  DESCRIBE DEPARTMENTS;
  

  1 constraint, and nobody can insert a new employee record into

  DESCRIBE DEPARTMENTS;
  

  8.

  CREATE OR REPLACE PROCEDURE add_employee (
    e_name   emp_tab.empname%TYPE,
    e_gender emp_tab.empgender%TYPE,
    e_number emp_tab.empno%TYPE,
    e_dept   emp_tab.deptno%TYPE
  ) AUTHID DEFINER IS
  BEGIN
    IF UPPER(e_gender) IN ('M','F') THEN
      INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
    END IF;
  END;
  /
   
  BEGIN
    add_employee ('Smith', 'H', 356, 20);
  END;
  /
  

  2 without specifying a non-

  DESCRIBE DEPARTMENTS;
  

  1 value for

   Name                                      Null?    Type
   ----------------------------------------- -------- ------------
   
   DEPARTMENT_ID                             NOT NULL NUMBER(4)
   DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
   MANAGER_ID                                         NUMBER(6)
   LOCATION_ID                                        NUMBER(4)
  

  2. You can insert a new employee record into

  DESCRIBE DEPARTMENTS;
  

  8.

  CREATE OR REPLACE PROCEDURE add_employee (
    e_name   emp_tab.empname%TYPE,
    e_gender emp_tab.empgender%TYPE,
    e_number emp_tab.empno%TYPE,
    e_dept   emp_tab.deptno%TYPE
  ) AUTHID DEFINER IS
  BEGIN
    IF UPPER(e_gender) IN ('M','F') THEN
      INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
    END IF;
  END;
  /
   
  BEGIN
    add_employee ('Smith', 'H', 356, 20);
  END;
  /
  

  2 without specifying a salary; therefore, the column

  DESCRIBE DEPARTMENTS;
  

  8.

  CREATE OR REPLACE PROCEDURE add_employee (
    e_name   emp_tab.empname%TYPE,
    e_gender emp_tab.empgender%TYPE,
    e_number emp_tab.empno%TYPE,
    e_dept   emp_tab.deptno%TYPE
  ) AUTHID DEFINER IS
  BEGIN
    IF UPPER(e_gender) IN ('M','F') THEN
      INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
    END IF;
  END;
  /
   
  BEGIN
    add_employee ('Smith', 'H', 356, 20);
  END;
  /
  

  2.

  INSERT INTO DEPARTMENTS (
   DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
  )
  VALUES (NULL, 'Sales', 200, 1700);
  

  3 does not have a

  DESCRIBE DEPARTMENTS;
  

  3

  DESCRIBE DEPARTMENTS;
  

  1 constraint.

• You want to allow index scans of the table, or allow an operation that requires indexing all rows.

  Oracle Database indexes do not store keys whose values are all

  DESCRIBE DEPARTMENTS;
  

  1. Therefore, for the preceding kinds of operations, at least one indexed column must have a

  DESCRIBE DEPARTMENTS;
  

  3

  DESCRIBE DEPARTMENTS;
  

  1 constraint.

uses the SQL*Plus command

INSERT INTO DEPARTMENTS (
 DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
)
VALUES (NULL, 'Sales', 200, 1700);

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

Example 12-3 Inserting NULL Values into Columns with NOT NULL Constraints

DESCRIBE DEPARTMENTS;

Result:

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 
 DEPARTMENT_ID                             NOT NULL NUMBER(4)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 MANAGER_ID                                         NUMBER(6)
 LOCATION_ID                                        NUMBER(4)

Try to insert

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

INSERT INTO DEPARTMENTS (
 DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
)
VALUES (NULL, 'Sales', 200, 1700);

Result:

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Omitting a value for a column that cannot be

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Result:

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

You can prevent the preceding error by giving

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DESCRIBE DEPARTMENTS;

You can combine

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

See Also:

• Oracle Database SQL Language Reference for more information about

  DESCRIBE DEPARTMENTS;
  

  3

  DESCRIBE DEPARTMENTS;
  

  1 constraint

• for more information about the usage of default column values

12.5 When to Use Default Column Values

When an

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Note:

Giving a column a non-

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

Use a default column value in these situations:

• The column has a

  DESCRIBE DEPARTMENTS;
  

  3

  DESCRIBE DEPARTMENTS;
  

  1 constraint.

  Giving the column a non-

  DESCRIBE DEPARTMENTS;
  

  1 default value prevents the error that would occur if someone inserted a row without specifying a value for the column.

• There is a most common value for the column.

  For example, if most departments in the company are in New York, then set the default value of the column

  CREATE OR REPLACE PROCEDURE add_employee (
    e_name   emp_tab.empname%TYPE,
    e_gender emp_tab.empgender%TYPE,
    e_number emp_tab.empno%TYPE,
    e_dept   emp_tab.deptno%TYPE
  ) AUTHID DEFINER IS
  BEGIN
    IF UPPER(e_gender) IN ('M','F') THEN
      INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
    END IF;
  END;
  /
   
  BEGIN
    add_employee ('Smith', 'H', 356, 20);
  END;
  /
  

  3.

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  32 to

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  33.

• There is a non-

  DESCRIBE DEPARTMENTS;
  

  1 value that signifies no entry.

  For example, if the value zero in the column

  CREATE OR REPLACE PROCEDURE add_employee (
    e_name   emp_tab.empname%TYPE,
    e_gender emp_tab.empgender%TYPE,
    e_number emp_tab.empno%TYPE,
    e_dept   emp_tab.deptno%TYPE
  ) AUTHID DEFINER IS
  BEGIN
    IF UPPER(e_gender) IN ('M','F') THEN
      INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
    END IF;
  END;
  /
   
  BEGIN
    add_employee ('Smith', 'H', 356, 20);
  END;
  /
  

  2.

  INSERT INTO DEPARTMENTS (
   DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
  )
  VALUES (NULL, 'Sales', 200, 1700);
  

  3 means that the salary has not yet been determined, then set the default value of that column to zero.

  A default column value that signifies no entry can simplify testing. For example, it lets you change this test:

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  3

  To this test:

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  4

• You want to automatically record the names of users who modify a table.

  For example, suppose that you allow users to insert rows into a table through a view. You give the base table a column named

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  37 (which need not be included in the definition of the view), to store the name of the user who inserted the row. To record the user name automatically, define a default value that invokes the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  38 function. For example:

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  5

See Also:

• Oracle Database SQL Language Reference for more information about the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  20 statement

• Oracle Database SQL Language Reference for more information about the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  40

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  23 statement

• Oracle Database SQL Language Reference for more information about the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  22

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  23 statement

• for information about the

  DESCRIBE DEPARTMENTS;
  

  3

  DESCRIBE DEPARTMENTS;
  

  1 constraint

12.6 Choosing a Primary Key for a Table (PRIMARY KEY Constraint)

The primary key of a table uniquely identifies each row and ensures that no duplicate rows exist (and typically, this is its only purpose). Therefore, a primary key value cannot be

DESCRIBE DEPARTMENTS;

A table can have at most one primary key, but that key can have multiple columns (that is, it can be a composite key). To designate a primary key, use the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Whenever practical, choose as the primary key a single column whose values are generated by a sequence.

The second-best choice for a primary key is a single column whose values are all of the following:

• Unique

• Never changed

• Never

  DESCRIBE DEPARTMENTS;
  

  1

• Short and numeric (and therefore easy to type)

Minimize your use of composite primary keys, whose values are long and cannot be generated by a sequence.

See Also:

• Oracle Database Concepts for general information about primary key constraints

• Oracle Database SQL Language Reference for complete information about primary key constraints, including restrictions

• Oracle Database SQL Language Reference for information about sequences

12.7 When to Use UNIQUE Constraints

Use a

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DESCRIBE DEPARTMENTS;

Unique KeyPrimary Key

Employee Social Security Number

Employee number

Truck license plate number

Truck number

Customer phone number (country code column, area code column, and local phone number column)

Customer number

Department name column and location column

Department number

shows a table with a

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Figure 12-1 Rows That Violate and Satisfy a UNIQUE Constraint

See Also:

• Oracle Database Concepts for general information about

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  16 constraints

• Oracle Database SQL Language Reference for complete information about

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  16 constraints, including restrictions

12.8 Enforcing Referential Integrity with FOREIGN KEY Constraints

When two tables share one or more columns, you use can use a

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Note:

A

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Designate one table as the referenced or parent table and the other as the dependent or child table. In the parent table, define either a

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

shows a foreign key defined on the department number. It guarantees that every value in this column must match a value in the primary key of the department table. This constraint prevents erroneous department numbers from getting into the employee table.

shows parent and child tables that share one column, a row that violates the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Figure 12-2 Rows That Violate and Satisfy a FOREIGN KEY Constraint

Topics:

See Also:

• Oracle Database Concepts for general information about foreign key constraints

• Oracle Database SQL Language Reference for complete information about foreign key constraints, including restrictions

12.8.1 FOREIGN KEY Constraints and NULL Values

Foreign keys allow key values that are all

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

• By default (without any

  DESCRIBE DEPARTMENTS;
  

  3

  DESCRIBE DEPARTMENTS;
  

  1 or

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  74 clauses), the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  55

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  48 constraint enforces the match none rule for composite foreign keys in the ANSI/ISO standard.

• To enforce the match full rule for

  DESCRIBE DEPARTMENTS;
  

  1 values in composite foreign keys, which requires that all components of the key be

  DESCRIBE DEPARTMENTS;
  

  1 or all be non-

  DESCRIBE DEPARTMENTS;
  

  1, define a

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  74 constraint that allows only all

  DESCRIBE DEPARTMENTS;
  

  1 or all non-

  DESCRIBE DEPARTMENTS;
  

  1 values in the composite foreign key. For example, with a composite key comprised of columns

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  83,

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  84, and

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  85:

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  6

• In general, it is not possible to use declarative referential integrity to enforce the match partial rule for

  DESCRIBE DEPARTMENTS;
  

  1 values in composite foreign keys, which requires the non-

  DESCRIBE DEPARTMENTS;
  

  1 portions of the key to appear in the corresponding portions in the primary or unique key of a single row in the referenced table. You can often use triggers to handle this case.

  See Also:

  Oracle Database PL/SQL Language Reference for more information about triggers

12.8.2 Defining Relationships Between Parent and Child Tables

Several relationships between parent and child tables can be determined by the other types of constraints defined on the foreign key in the child table.

No Constraints on the Foreign Key

When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value. This model allows nulls in the foreign key.

This model establishes a one-to-many relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. An example of such a relationship is shown in between the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

NOT NULL Constraint on the Foreign Key

When nulls are not allowed in a foreign key, each row in the child table must explicitly reference a value in the parent key because nulls are not allowed in the foreign key.

Any number of rows in the child table can reference the same parent key value, so this model establishes a one-to-many relationship between the parent and foreign keys. However, each row in the child table must have a reference to a parent key value; the absence of a value (a null) in the foreign key is not allowed. The same example in the previous section illustrates such a relationship. However, in this case, employees must have a reference to a specific department.

UNIQUE Constraint on the Foreign Key

When a

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

This model establishes a one-to-one relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. For example, assume that the employee table had a column named

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

• To enforce referential integrity rules between the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  95 and

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  92 tables (the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  97 constraint)

• To guarantee that each employee has a unique membership number (the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  16 key constraint)

UNIQUE and NOT NULL Constraints on the Foreign Key

When both

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

This model establishes a one-to-one relationship between the parent and foreign keys that does not allow undetermined values (nulls) in the foreign key. If you expand the previous example by adding a

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

12.8.3 Rules for Multiple FOREIGN KEY Constraints

Oracle Database allows a column to be referenced by multiple

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

12.8.4 Deferring Constraint Checks

When Oracle Database checks a constraint, it signals an error if the constraint is not satisfied. To defer checking constraints until the end of the current transaction, use the

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Note:

You cannot use the

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

When deferring constraint checks:

• Select appropriate data.

  You might want to defer constraint checks on

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  16 and

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  55 keys if the data you are working with has any of these characteristics:

  • Tables are snapshots.

  • Some tables contain a large amount of data being manipulated by another application, which might not return the data in the same order.

• Update cascade operations on foreign keys.

• Ensure that constraints are deferrable.

  After identifying the appropriate tables, ensure that their

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  55,

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  16 and

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  47 key constraints are created

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  18.

• Within the application that manipulates the data, set all constraints deferred before you begin processing any data, as follows:

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  7

• (Optional) Check for constraint violations immediately before committing the transaction.

  Immediately before the

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  19 statement, run the

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  09

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  10

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  22

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  23 statement. If there are any problems with a constraint, this statement fails, and identifies the constraint that caused the error. If you commit while constraints are violated, the transaction rolls back and you get an error message.

In , the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Example 12-4 Deferring Constraint Checks

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Query:

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Result:

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Update:

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Result:

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Query:

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Result:

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

The

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

See Also:

Oracle Database SQL Language Reference for more information about the

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

12.9 Minimizing Space and Time Overhead for Indexes Associated with Constraints

When you create a

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

When Oracle Database uses a unique index to enforce a constraint, and constraints associated with the unique index are dropped or disabled, the index is dropped. To preserve the statistics associated with the index (which would take a long time to re-create), specify the

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

While enabled foreign keys reference a

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Note:

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

To use existing indexes when creating unique and primary key constraints, include

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

See Also:

Oracle Database SQL Language Reference for more details and examples of integrity constraints

12.10 Guidelines for Indexing Foreign Keys

Index foreign keys unless the matching unique or primary key is never updated or deleted.

See Also:

Oracle Database Concepts for more information about indexing foreign keys

12.11 Referential Integrity in a Distributed Database

The declaration of a referential constraint cannot specify a foreign key that references a primary or unique key of a remote table.

However, you can maintain parent/child table relationships across nodes using triggers.

See Also:

Oracle Database PL/SQL Language Reference for more information about triggers that enforce referential integrity

Note:

If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can make both the parent table and the child table inaccessible.

For example, assume that the child table is in the

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

If the network connection between the two databases fails, then some data manipulation language (DML) statements against the child table (those that insert rows or update a foreign key value) cannot proceed, because the referential integrity triggers must have access to the parent table in the

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

12.12 When to Use CHECK Constraints

Use

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

See Also:

Examples of

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

• A

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  74 constraint on employee salaries so that no salary value is greater than 10000.

• A

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  74 constraint on department locations so that only the locations "

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  61", "

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  62

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  63", and "

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  64" are allowed.

• A

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  74 constraint on the salary and commissions columns to prevent the commission from being larger than the salary.

12.12.1 Restrictions on CHECK Constraints

A

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

• The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated.

• The condition cannot contain subqueries or sequences.

• The condition cannot include the

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  68,

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  69,

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  38, or

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  71 SQL functions.

• The condition cannot contain the pseudocolumns

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  72 or

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  73.

• The condition cannot contain the

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  74 operator.

• The condition cannot contain a user-defined function.

See Also:

• Oracle Database SQL Language Reference for information about the

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  72 pseudocolumn

• Oracle Database SQL Language Reference for information about the

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  73 pseudocolumn

• Oracle Database SQL Language Reference for information about the

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  74 operator (used in hierarchical queries)

12.12.2 Designing CHECK Constraints

When using

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

For example, consider this

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

At first glance, this rule may be interpreted as "do not allow a row in the employee table unless the employee salary is greater than zero or the employee commission is greater than or equal to zero." But if a row is inserted with a null salary, that row does not violate the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Note:

If you are not sure when unknown values result in

DESCRIBE DEPARTMENTS;

12.12.3 Rules for Multiple CHECK Constraints

A single column can have multiple

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

The order in which the constraints are evaluated is not defined, so be careful not to rely on the order or to define multiple constraints that conflict with each other.

12.12.4 Choosing Between CHECK and NOT NULL Constraints

According to the ANSI/ISO standard, a

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Therefore, you can write

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

In the case where a composite key can allow only all

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

12.13 Examples of Defining Constraints

and show how to create simple constraints during the prototype phase of your database design. In these examples, each constraint is given a name. Naming the constraints prevents the database from creating multiple copies of the same constraint, with different system-generated names, if the data definition language (DDL) statement runs multiple times.

creates tables and their constraints at the same time, using the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

Example 12-5 Defining Constraints with the CREATE TABLE Statement

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

creates constraints for existing tables, using the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

You cannot create a validated constraint on a table if the table contains rows that violate the constraint.

Example 12-6 Defining Constraints with the ALTER TABLE Statement

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

See Also:

Oracle Database Administrator's Guide for information about creating and maintaining constraints for a large production database

12.13.1 Privileges Needed to Define Constraints

If you have the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

If you have the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

If you have the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

You can define

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

See Also:

12.13.2 Naming Constraints

Assign names to constraints

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

Choosing your own name makes error messages for constraint violations more understandable, and prevents the creation of duplicate constraints with different names if the SQL statements are run more than once.

See the previous examples of the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

See Also:

"" for examples of static data dictionary views

12.14 Enabling and Disabling Constraints

This section explains the mechanisms and procedures for manually enabling and disabling constraints.

enabled constraint. When a constraint is enabled, the corresponding rule is enforced on the data values in the associated columns. The definition of the constraint is stored in the data dictionary.

disabled constraint. When a constraint is disabled, the corresponding rule is not enforced. The definition of the constraint is still stored in the data dictionary.

An integrity constraint represents an assertion about the data in a database. This assertion is always true when the constraint is enabled. The assertion might not be true when the constraint is disabled, because data that violates the integrity constraint can be in the database.

Topics:

12.14.1 Why Disable Constraints?

During day-to-day operations, keep constraints enabled. In certain situations, temporarily disabling the constraints of a table makes sense for performance reasons. For example:

• When loading large amounts of data into a table using SQL*Loader

• When performing batch operations that make massive changes to a table (such as changing each employee number by adding 1000 to the existing number)

• When importing or exporting one table at a time

Temporarily turning off constraints can speed up these operations.

12.14.2 Creating Enabled Constraints (Default)

When you define an integrity constraint (using either

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Example 12-7 Creating Enabled Constraints

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Include the

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

An

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

See Also:

, for more information about rows that violate constraints

12.14.3 Creating Disabled Constraints

You define and disable an integrity constraint (using either

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Example 12-8 Creating Disabled Constraints

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Include the

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

An

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

12.14.4 Enabling Existing Constraints

After you have cleansed the data and filled the empty columns, you can enable constraints that were disabled during data insertion.

To enable an existing constraint, use the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Example 12-9 Enabling Existing Constraints

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

An

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

See Also:

, for more information about rows that violate constraints

12.14.5 Disabling Existing Constraints

If you must perform a large insert or update when a table contains data, you can temporarily disable constraints to improve performance of the bulk operation.

To disable an existing constraint, use the

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Example 12-10 Disabling Existing Constraints

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

12.14.6 Guidelines for Enabling and Disabling Key Constraints

When enabling or disabling

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

See Also:

Oracle Database Administrator's Guide and

12.14.7 Fixing Constraint Exceptions

If a row of a table disobeys an integrity constraint, then this row is in violation of the constraint and is called an exception to the constraint. If any exceptions exist, then the constraint cannot be enabled. The rows that violate the constraint must be updated or deleted before the constraint can be enabled.

You can identify exceptions for a specific integrity constraint as you try to enable the constraint.

See Also:

, for more information about this procedure

When you try to create or enable a constraint, and the statement fails because integrity constraint exceptions exist, the statement is rolled back. You cannot enable the constraint until all exceptions are either updated or deleted. To determine which rows violate the integrity constraint, include the

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

See Also:

Oracle Database Administrator's Guide for more information about responding to constraint exceptions

12.15 Modifying Constraints

Starting with Oracle8i, you can modify an existing constraint with the

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

See Also:

Oracle Database SQL Language Reference for information about the parameters you can modify

Example 12-11 Modifying Constraints

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

12.16 Renaming Constraints

One property of a constraint that you can modify is its name. Situations in which you would rename a constraint include:

• You want to clone a table and its constraints.

  Constraint names must be unique, even across multiple schemas. Therefore, the constraints in the original table cannot have the same names as those in the cloned table.

• You created a constraint with a default system-generated name, and now you want to give it a name that is easy to remember, so that you can easily enable and disable it.

shows how to find the system-generated name of a constraint and change it.

Example 12-12 Renaming a Constraint

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Query:

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Result (system-generated name of constraint name varies):

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Rename constraint from name reported in preceding query to

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Query:

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Result:

empgender VARCHAR2(1)

12.17 Dropping Constraints

You can drop a constraint using the

ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

• The constraint enforces a rule that is no longer true.

• The constraint is no longer needed.

To drop a constraint and all other integrity constraints that depend on it, specify

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

Example 12-13 Dropping Constraints

empgender VARCHAR2(1)

When dropping

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

See Also:

• Oracle Database SQL Language Reference for more information about the

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  42 clause of the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  22

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  23 statement.

• Oracle Database Administrator's Guide for more information about dropping constraints.

• Oracle Database SQL Language Reference for information about the

  ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);
  

  87

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  10 clause of the

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  42

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  23 statement, which drops all referential integrity constraints that refer to primary and unique keys in the dropped table

12.18 Managing FOREIGN KEY Constraints

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

12.18.1 Data Types and Names for Foreign Key Columns

You must use the same data type for corresponding columns in the dependent and referenced tables. The column names need not match.

12.18.2 Limit on Columns in Composite Foreign Keys

Because foreign keys reference primary and unique keys of the parent table, and

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

12.18.3 Foreign Key References Primary Key by Default

If the column list is not included in the

ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

12.18.4 Privileges Required to Create FOREIGN KEY Constraints

To create a

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

• Parent Table The creator of the referential integrity constraint must own the parent table or have

  ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);
  

  27 object privileges on the columns that constitute the parent key of the parent table.

• Child Table The creator of the referential integrity constraint must have the ability to create tables (that is, the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  40

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  23 or

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  40

  ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);
  

  14

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  23 system privilege) or the ability to alter the child table (that is, the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  22 object privilege for the child table or the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  22

  ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);
  

  14

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  23 system privilege).

In both cases, necessary privileges cannot be obtained through a role; they must be explicitly granted to the creator of the constraint.

These restrictions allow:

• The owner of the child table to explicitly decide which constraints are enforced and which other users can create constraints

• The owner of the parent table to explicitly decide if foreign keys can depend on the primary and unique keys in her tables

12.18.5 Choosing How Foreign Keys Enforce Referential Integrity

Oracle Database allows different types of referential integrity actions to be enforced, as specified with the definition of a

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

• Prevent Delete or Update of Parent Key The default setting prevents the deletion or update of a parent key if there is a row in the child table that references the key. For example:

  empgender VARCHAR2(1)
  

  2

• Delete Child Rows When Parent Key Deleted The

  empgender VARCHAR2(1)
  

  25

  empgender VARCHAR2(1)
  

  26

  ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);
  

  87 action allows parent key data that is referenced from the child table to be deleted, but not updated. When data in the parent key is deleted, all rows in the child table that depend on the deleted parent key values are also deleted. To specify this referential action, include the

  empgender VARCHAR2(1)
  

  25

  empgender VARCHAR2(1)
  

  26

  ALTER TABLE emp_tab ADD FOREIGN KEY (deptno) REFERENCES dept_tab(deptno);
  

  87 option in the definition of the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  55

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  48 constraint. For example:

  empgender VARCHAR2(1)
  

  3

• Set Foreign Keys to Null When Parent Key Deleted The

  empgender VARCHAR2(1)
  

  25

  empgender VARCHAR2(1)
  

  26

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  09

  DESCRIBE DEPARTMENTS;
  

  1 action allows data that references the parent key to be deleted, but not updated. When referenced data in the parent key is deleted, all rows in the child table that depend on those parent key values have their foreign keys set to

  DESCRIBE DEPARTMENTS;
  

  1. To specify this referential action, include the

  empgender VARCHAR2(1)
  

  25

  empgender VARCHAR2(1)
  

  26

  ALTER TABLE dept_tab ADD PRIMARY KEY (deptno);
  

  09

  DESCRIBE DEPARTMENTS;
  

  1 option in the definition of the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  55

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  48 constraint. For example:

  empgender VARCHAR2(1)
  

  4

12.19 Viewing Information About Constraints

To find the names of constraints, what columns they affect, and other information to help you manage them, query the static data dictionary views

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

empgender VARCHAR2(1)

See Also:

Oracle Database Reference for information about

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

empgender VARCHAR2(1)

Example 12-14 Viewing Information About Constraints

empgender VARCHAR2(1)

List accessible constraints in DeptTab and EmpTab:

empgender VARCHAR2(1)

Result:

empgender VARCHAR2(1)

Distinguish between

DESCRIBE DEPARTMENTS;

DESCRIBE DEPARTMENTS;

DROP TABLE emp_tab;
CREATE TABLE emp_tab (
  empname VARCHAR2(80),
  empno   INTEGER,
  deptno  INTEGER
);

empgender VARCHAR2(1)

empgender VARCHAR2(1)

empgender VARCHAR2(1)

Result:

empgender VARCHAR2(1)

For

empgender VARCHAR2(1)

empgender VARCHAR2(1)

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

Result:

CREATE OR REPLACE PROCEDURE add_employee (
  e_name   emp_tab.empname%TYPE,
  e_gender emp_tab.empgender%TYPE,
  e_number emp_tab.empno%TYPE,
  e_dept   emp_tab.deptno%TYPE
) AUTHID DEFINER IS
BEGIN
  IF UPPER(e_gender) IN ('M','F') THEN
    INSERT INTO emp_tab VALUES (e_name, e_gender, e_number, e_dept);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Gender must be M or F.');
  END IF;
END;
/
 
BEGIN
  add_employee ('Smith', 'H', 356, 20);
END;
/

Note that:

• Some constraint names are user specified (such as

  empgender VARCHAR2(1)
  

  55), while others are system specified (such as

  empgender VARCHAR2(1)
  

  56).

• Each constraint type is denoted with a different character in the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  59 column. This table summarizes the characters used for each constraint type:

  Constraint TypeCharacter

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  47

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  48

  empgender VARCHAR2(1)
  

  60

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  16

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  48

  empgender VARCHAR2(1)
  

  63

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  55

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  48

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  60

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  74,

  DESCRIBE DEPARTMENTS;
  

  3

  DESCRIBE DEPARTMENTS;
  

  1

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  85

  Note:

  An additional constraint type is indicated by the character "

  empgender VARCHAR2(1)
  

  71" in the

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  59 column. This constraint type corresponds to constraints created using the

  empgender VARCHAR2(1)
  

  73

  DROP TABLE emp_tab;
  CREATE TABLE emp_tab (
    empname VARCHAR2(80),
    empno   INTEGER,
    deptno  INTEGER
  );
  

  74

  empgender VARCHAR2(1)
  

  75 for views.

  What is an important aspect of referential integrity?

  Referential integrity is a term used in database design to describe the relationship between two tables. It is important because it ensures that all data in a database remains consistent and up to date. It helps to prevent incorrect records from being added, deleted, or modified.

  What is the purpose of enforcing referential integrity quizlet?

  Enforcing referential integrity assures that you cannot enter a value in the foreign key if there is no corresponding value in the primary key. A collection of data organized in a manner that allows access, retrieval and use of that data. 1.

  What is the referential integrity rule quizlet?

  The referential integrity rule states that a value of a foreign key in one relation must match a value of the primary key in the related relation (or be null).

  Which SQL keyword is used to specify a condition that rows must meet?

  In a SQL statement, the WHERE clause specifies criteria that field values must meet for the records that contain the values to be included in the query results.