3.3 Foreign Key

Foreign key constraints enforce referential integrity. A foreign key constraint restricts the domain of a column value. An example is to restrict a STATE abbreviation to a limited set of values in another control structure ”that being a parent table.

The term "lookup" is often used when referring to tables that provide this type of reference information. In some applications, these tables are created with this keyword ”a practice we'll use here with the example STATE_LOOKUP.

Start with creating a lookup table that provides a complete list of state abbreviations. Then use referential integrity to ensure that students have valid state abbreviations. The first table is the state lookup table with STATE as the primary key.

 CREATE TABLE state_lookup  (state      VARCHAR2(2),   state_desc VARCHAR2(30)) TABLESPACE student_data; ALTER TABLE state_lookup   ADD CONSTRAINT pk_state_lookup PRIMARY KEY (state)   USING INDEX TABLESPACE student_index; 

To insert a few rows:

 INSERT INTO state_lookup VALUES ('CA', 'California'); INSERT INTO state_lookup VALUES ('NY', 'New York'); INSERT INTO state_lookup VALUES ('NC', 'North Carolina'); 

We enforce referential integrity by implementing the parent “child relationship, graphically shown in Figure 3-2.

Figure 3-2. Foreign Key with State Lookup.

Figure 3-2 shows a one-to-many relationship between the STATE_LOOKUP table and the STUDENTS table. The STATE_LOOKUP table defines the "universal set" of state abbreviations ”each state being represented once in that table; hence, a primary key on the STATE column of STATE_LOOKUP.

A state from the STATE_LOOKUP table can appear multiple times in the STUDENTS table. There can be many students from a single state. Hence referential integrity implements a one-to-many relationship between STATE_LOOKUP and STUDENTS.

The foreign key also ensures the integrity of the STATE column in the STUDENTS table ”a student with a driver's license will always have a state abbreviation that is a member of the STATE_LOOKUP table.

The foreign key constraint is created on the child table. Following is the STUDENTS table with a foreign key constraint. With an ALTER TABLE statement, we declare the column STATE to have a foreign key constraint that references the primary key column of the STATE_LOOKUP table.

Script 3-1 Foreign Key with State Lookup DDL.

 CREATE TABLE students  (student_id    VARCHAR2(10) NOT NULL,   student_name  VARCHAR2(30) NOT NULL,   college_major VARCHAR2(15) NOT NULL,   status        VARCHAR2(20) NOT NULL,   state         VARCHAR2(2),   license_no    VARCHAR2(30)) TABLESPACE student_data; ALTER TABLE students   ADD CONSTRAINT pk_students PRIMARY KEY (student_id)   USING INDEX TABLESPACE student_index; ALTER TABLE students   ADD CONSTRAINT uk_students_license   UNIQUE (state, license_no)   USING INDEX TABLESPACE student_index; ALTER TABLE students    ADD CONSTRAINT ck_students_st_lic    CHECK ((state IS NULL AND license_no IS NULL) OR           (state IS NOT NULL AND license_no is NOT NULL)); ALTER TABLE students   ADD CONSTRAINT fk_students_state   FOREIGN KEY (state) REFERENCES state_lookup (state); 

The DDL script in Script 3-1 creates the STUDENTS table and table constraints. These constraints enforce the following rules:

Rule	Enforced With
A student is uniquely identified by a STUDENT_ID.	PRIMARY KEY constraint.
A student MAY have a driver's license. If they do, that state and license combination is unique among all other students.	UNIQUE constraint on STATE and LICENSE.
If STATE is NULL then LICENSE_NO must be NULL; otherwise both must be NOT NULL.	CHECK constraint on STATE and LICENSE.
A student MAY have a column value for STATE. If they do, the STATE abbreviation is valid with respect to the STATE_LOOKUP table.	FOREIGN KEY constraint on STATE.

3.3.1 Four Types of Errors

The rules of referential integrity are enforced during updates and deletes on parent tables and inserts and updates on child tables. The SQL statements affected by referential integrity are:

PARENT-UPDATE	You cannot UPDATE a STATE in STATE_LOOKUP with a value so as to leave students with a state abbreviation that is no longer in the STATE_LOOKUP table.
PARENT-DELETE	You cannot DELETE a STATE and leave students with a state that is no longer in the parent lookup table. For example, if there are students with a California license, which use the abbreviation 'CA,' you cannot delete the 'CA' row from STATE_LOOKUP.
CHILD-INSERT	You cannot INSERT a student with a state that is not found in the STATE_LOOKUP table. For example, you cannot insert a student with a license and set the STATE column to a value not found in the STATE_LOOKUP table.
CHILD-UPDATE	You cannot UPDATE a student and replace the state with a state not found in the parent state lookup table.

The following SQL statements demonstrate the four error types and the Oracle error returned when the constraint is violated. These inserts, updates, and deletes behave assuming the data for the STATE_LOOKUP and STUDENTS tables:

STATE_LOOKUP

State	State Description
CA	California
NY	New York
NC	North Carolina

STUDENTS

Student ID	Student Name	College Major	Status	State	License NO
A101	John	Biology	Degree	NULL	NULL
A102	Mary	Math/Science	Degree	NULL	NULL
A103	Kathryn	History	Degree	CA	MV-232-13
A104	Steven	Biology	Degree	NY	MV-232-14
A105	William	English	Degree	NC	MV-232-15

The first two SQL statements are changes to the parent table. Prior to changing the parent, Oracle must examine the contents of the child table to ensure data integrity.

PARENT-UPDATE

  SQL>  UPDATE state_lookup  2  SET state = 'XX'  3  WHERE state = 'CA';  UPDATE state_lookup   *   ERROR at line 1:   ORA-02292: integrity constraint (SCOTT.FK_STUDENTS_STATE)   violated  child record found  

PARENT-DELETE

  SQL>  DELETE FROM state_lookup  2  WHERE state = 'CA';  DELETE FROM state_lookup   *   ERROR at line 1:   ORA-02292: integrity constraint (SCOTT.FK_STUDENTS_STATE)   violated  child record found  

The next two statements are changes to the child table. Each DML on the child requires that Oracle examine the contents of the parent to ensure data integrity.

CHILD-INSERT

  SQL>  INSERT INTO STUDENTS  2  VALUES ('A000',  3  'Joseph','History','Degree','XX','MV-232-00');  INSERT INTO STUDENTS   *   ERROR at line 1:   ORA-02291: integrity constraint (SCOTT.FK_STUDENTS_STATE)   violated - parent key not found  

CHILD-UPDATE

  SQL>  UPDATE students  2  SET state = 'XX'  3  WHERE student_id = 'A103';  UPDATE students   *   ERROR at line 1:   ORA-02291: integrity constraint (SCOTT.FK_STUDENTS_STATE)   violated - parent key not found  

For Oracle to enforce these four rules, it must read from both tables. A simple INSERT into a STUDENTS table, given a foreign key constraint, requires that Oracle read the STATE_LOOKUP table. If we DELETE from the STATE_LOOKUP table, then Oracle must read the STUDENTS table to first ensure there is no student row that contains a STATE value that references the STATE_LOOKUP row.

In each of the four types of errors above, the error number is the same: ORA-02291.

Referential integrity is a critical part of a database design. It is rare for a table to not be either a parent or child of some other table. If you ever look at a data model printed on a large scale graphics plotter, the first thing you will notice are tables with no lines ”no parents and no children.

3.3.2 Delete Cascade

You have the option within the foreign key syntax to specify a delete cascade feature. This feature only affects delete statements in the parent table.

With this option, a delete from the parent will automatically delete all relevant children. Had we created the foreign key constraint in Script 3-1, "Foreign Key," with the DELETE CASCADE option, then the following SQL would delete the record in the STATE_LOOKUP table for California plus all students that have a California license.

  SQL>  DELETE FROM state_lookup  2  WHERE state = 'CA'; 

The DELETE CASCADE syntax is:

 ON DELETE CASCADE 

The syntax for the foreign constraint, shown in Script 3-1, can be rewritten with the cascade option as follows :

 ALTER TABLE students   ADD CONSTRAINT fk_students_state   FOREIGN KEY (state) REFERENCES state_lookup (state)   ON DELETE CASCADE; 

The delete cascade option should be the exception rather than the rule. Lots of data can be inadvertently lost with an accidental delete of a row in the parent lookup table. There are applications where this option is very useful. If data is temporary ”only lives for a short time and is eventually deleted ”then this is very convenient .

Delete cascade can span multiple tables. A parent can cascade to a child and that child can cause a delete cascade to other tables. If there is a foreign key constraint in the chain, without the cascade option, the delete from the parent fails.

Deletes that cascade over several tables can potentially affect other parts of the system. A lengthy delete that spans tables and deletes millions of rows will require comparable rollback space to write undo information. Rollback space should be analyzed if the cascade delete is excessive. Additionally, performance of concurrent queries against the tables will be affected. Consider the following when declaring a delete cascade option.

• Does the cascade fit the application? An accidental delete from a parent look table should not delete customer accounts.

• What is the chain being declared? Look at what tables cascade to other tables. Consider the potential impact and magnitude of a delete and how it would impact performance.

3.3.3 Mandatory Foreign Key Columns

The foreign key constraints stipulates the rule that a child MAY be a member of the parent. If the child is a member then there must be integrity. A NOT NULL constraint on the foreign key replaces MAY with MUST.

The foreign key constraint in Script 3-1 enforces the rule.

• A student MAY have a driver's license. If they do, that state is a member of STATE_LOOKUP.

This rule can have a different flavor ”restated here:

• A student MUST have a driver's license and the state is a member of STATE_LOOKUP.

The latter is still a statement of referential integrity; there still exists a one-to-many relationship. The rule changes to MUST when the NOT NULL constraint is declared on the child column. The business rules are revised. Because the STATE and LICENSE are mandatory, the CHECK constraint is removed. The foreign key relationship changes from MAY to MUST.

Script 3-2 DDL for the STUDENTS and STATE_LOOKUP tables enforces the following:

Rule	Enforced With
A student is uniquely identified by a STUDENT_ID.	PRIMARY KEY constraint
A student MUST have a driver's license. The state and license combination is unique among all other students.	UNIQUE constraint, NOT NULL on STATE, NOT NULL on LICENSE
A student MUST have a column value for STATE. The STATE abbreviation is valid with respect to the STATE_LOOKUP table.	FOREIGN KEY constraint, NOT NULL on STATE

The DDL to enforce these rules is similar to Script 3-1. There is no CHECK constraint and NOT NULL constraints are added. There is no change to the STATE_LOOKUP table to accommodate the rule changes.

Script 3-2 Foreign Key with Mandatory Constraints.

 CREATE TABLE students  (student_id    VARCHAR2(10) NOT NULL,   student_name  VARCHAR2(30) NOT NULL,   college_major VARCHAR2(15) NOT NULL,   status        VARCHAR2(20) NOT NULL,   state         VARCHAR2(2)  NOT NULL,   license_no    VARCHAR2(30) NOT NULL) TABLESPACE student_data; ALTER TABLE students   ADD CONSTRAINT pk_students PRIMARY KEY (student_id)   USING INDEX TABLESPACE student_index; ALTER TABLE students   ADD CONSTRAINT uk_students_license   UNIQUE (state, license_no)   USING INDEX TABLESPACE student_index; ALTER TABLE students   ADD CONSTRAINT fk_students_state   FOREIGN KEY (state) REFERENCES state_lookup (state); 

3.3.4 Referencing the Parent Syntax

When you create a foreign key constraint, the column(s) on that foreign key reference the column(s) that make up a PRIMARY KEY or UNIQUE constraint. In the case where a foreign key references a parent primary key, the column does not need to be specified. For example, Script 3-2 uses the following syntax:

 ALTER TABLE students   ADD CONSTRAINT fk_students_state   FOREIGN KEY (state) REFERENCES state_lookup (state); 

When no parent column is referenced, the default referenced column is the primary key of the parent. The following would work just as well.

 ALTER TABLE students   ADD CONSTRAINT fk_students_state   FOREIGN KEY (state) REFERENCES state_lookup; 

When your foreign key references a parent column with a unique constraint, that parent column must be specified in the ADD CONSTRAINT statement.

3.3.5 Referential Integrity across Schemas and Databases

A foreign key in a table can refer to a parent table in another schema. This is not desirable, at least from a database administrator's perspective. An application that sits wholly within a single schema is very portable. The entire application that exports as a single owner easily imports into another database. When functionality spans schemas, the migration is not such a smooth process. Often, the application is not fully ported because the functionality in the other schemas is overlooked in the port. An export dump file will import with errors. The foreign key constraints will fail because the referenced schema does not exist, the object in the referenced schema does not exist, or privileges from the referenced schema have not been created.

Referential integrity can be implemented, to various degrees, across databases. This can be complicated. Options are to use triggers, refreshed materialized views, or Oracle replication. Materialized views are a straightforward and balanced solution. The materialized view becomes an object in the local schema. It is refreshed using data from another database. A database link is required along with the CREATE ANY MATERIALIZED VIEW privilege. The refresh rate of the view does not make the data real-time but can be near real-time if the materialized view is refreshed frequently.

Assume that the students' database needs relatively fresh data from a government driver's license agency. The students' database needs data from a LICENSE table. In this context the license agency is the database and the LICENSE table is a master table.

The LICENSE table exists on the government agency server, AGENCY.GOV, in a database name PROD, with a username/password of SCOTT/TIGER. The LICENSE table must have a primary key. The objective is to have a local snapshot of the LICENSE table that is refreshed once every 15 minutes. This is not real-time but certainly satisfies the needs of the student's database. The remote connectivity is illustrated in Figure 3-3.

Figure 3-3. Materialized View.

The process starts by creating a Materialized View Log of the LICENSE table on the PROD.AGENCY.GOV database. This log is used by Oracle to track changes to the master table. This data in log format is used to refresh the materialized view in the student's database.

On the remote database:

 CREATE MATERIALIZED VIEW LOG ON LICENSE; 

On the students' database, create a link to the remote PROD database; then create the materialized view. This view will be refreshed once every 15 minutes. If the network is down, the student's application still has access to the most recent refresh.

 CREATE DATABASE LINK prod.agency.gov   CONNECT TO scott IDENTIFIED BY tiger   USING 'prod.agency.gov'; CREATE MATERIALIZED VIEW STUDENT_LICENSE_RECORDS  REFRESH FAST NEXT SYSDATE + 1/96  AS SELECT * FROM [e-mail] 

The students' tables can now reference a local snapshot of data from the government database table with license information.

Creating materialized views requires non-default privileges. The materialized view should be created with storage clauses based on the size of the snapshot.

3.3.6 Multiple Parents and DDL Migration

A child table frequently has multiple lookup tables. The use of lookup tables greatly enhances the overall integrity of the data. The STUDENTS table began with a two-character column STATE. Integrity was added to that column by creating a lookup table, STATE_LOOKUP. Once the STATE_LOOKUP table is populated , the foreign key constraint can be created. The domain of STATE abbreviations and state descriptions can grow without impact to child tables. Lookup tables are often added throughout the development process as a means to improve the integrity of the data.

From an end user's perspective, maintenance of lookup tables usually addresses the subject of end user roles and privileges. For any application there are specific roles and privileges to run the application. However, changes to tables such as STATE_LOOKUP would, and should, require special access.

Developing the application software to support end user maintenance of lookup tables is mostly cut-and-paste. Most lookup tables have two columns. Once the first lookup maintenance screen is developed, maintenance for other lookup tables is somewhat repetitious.

Application code to display lookup data follows a standard practice of never showing the primary key column. To display student data on a screen requires joining the STUDENTS and STATE_LOOKUP tables. For each student, the student name and state description are shown but not the two character state field. HTML form elements such as drop down lists are populated with the state description, but by using the state as the option value.

 <OPTION VALUE=state>state_description</OPTION> 

An observation of the data outlined in the tables on p. 108 might lead one to believe that student college majors should be in a lookup table as well. This additional lookup table would restrict the domain of college major descriptions to the values controlled through a lookup table. The MAJOR_LOOKUP table would store the descriptions like "Biology" and "Math." This addition would be modeled with the entity diagram shown in Figure 3-4.

Figure 3-4. Multiple Parents.

The addition of a MAJOR_LOOKUP table to a STUDENTS and STATE_LOOKUP, illustrated in Figure 3-2, is accomplished with the following. Assume there is data present in the STATE_LOOKUP and STUDENTS table as outlined in on p. 108.

The lookup table, MAJOR_LOOKUP, must be created and populated. The following includes the CREATE script, primary key, and data load.

 CREATE TABLE major_lookup  (major      VARCHAR2(2)  NOT NULL,   major_desc VARCHAR2(15) NOT NULL) TABLESPACE student_data; INSERT INTO major_lookup values ('UD','Undeclared'); INSERT INTO major_lookup values ('BI','Biology'); INSERT INTO major_lookup values ('MS','Math/Science'); INSERT INTO major_lookup values ('HI','History'); INSERT INTO major_lookup values ('EN','English'); ALTER TABLE major_lookup   ADD CONSTRAINT pk_major_lookup PRIMARY KEY (major)   USING INDEX TABLESPACE student_index; 

The STUDENTS table must be changed. It stores the college major as VARCHAR(15). This must be replaced with a two-character field that will be a foreign key to the MAJOR_LOOKUP table. This approach creates a temporary copy (table TEMP) of the STUDENTS table, including the data. The STUDENTS table is dropped; a new STUDENTS table is created and the saved data is migrated back into the new STUDENTS table.

 CREATE TABLE TEMP AS SELECT * FROM STUDENTS; DROP TABLE STUDENTS; CREATE TABLE students  (student_id      VARCHAR2(10) NOT NULL,   student_name    VARCHAR2(30) NOT NULL,   college_major   VARCHAR2(2)  NOT NULL,   status          VARCHAR2(15) NOT NULL,   state           VARCHAR2(2),   license_no      VARCHAR2(30)) TABLESPACE student_data; INSERT INTO STUDENTS SELECT student_id,        student_name,        decode        (college_major,          'Undeclared'   , 'UD',          'Biology'      , 'BI',          'Math/Science' , 'MS',          'History'      , 'HI',          'English'      , 'EN'),        status,        state,        license_no FROM temp; 

The new STUDENTS table is populated, but with BI for Biology. The constraints are added to the STUDENTS table. This includes:

1. PRIMARY KEY

2. UNIQUE constraint on STATE, LICENSE No

3. CHECK constraint on STATE and License No

4. Foreign Key to STATE_LOOKUP

5. Foreign Key to MAJOR_LOOKUP

 1. ALTER TABLE students    ADD CONSTRAINT pk_students PRIMARY KEY (student_id)    USING INDEX TABLESPACE student_index; 2. ALTER TABLE students    ADD CONSTRAINT uk_students_license    UNIQUE (state, license_no)    USING INDEX TABLESPACE student_index; 3. ALTER TABLE students    ADD CONSTRAINT ck_students_st_lic    CHECK ((state IS NULL AND license_no IS NULL) OR           (state IS NOT NULL AND license_no is NOT NULL)); 4. ALTER TABLE students    ADD CONSTRAINT fk_students_state    FOREIGN KEY (state) REFERENCES state_lookup; 5. ALTER TABLE students    ADD CONSTRAINT fk_students_college_major    FOREIGN KEY (college_major) REFERENCES major_lookup; 

3.3.7 Many-to-Many Relationships

Data modeling tools will draw, at the logical level, a many-to-many relationship with the notation show in Figure 3-5.

Figure 3-5. Many-to-Many Relationship.

The model in Figure 3-5 demonstrates that a student can take several courses, while each course is taught to more than one student. There is a many-to-many relationship between students and courses. Physically, this is not implemented directly; rather, we include a cross-reference table. That cross-reference table, STUDENTS_COURSES, will contain all the courses a student takes, plus all students that take a particular course. The physical model becomes the graph in Figure 3-6.

Figure 3-6. Physical Many-to-Many Relationship.

The following types of constraints are commonly applied with the physical implementation of a many-to-many relationship.

• We have a primary key in STUDENTS.

• We have a primary key in COURSES.

• We have a CONCATENATED PRIMARY KEY in the cross-reference table, STUDENTS_COURSES.

• Part of this primary key is a foreign key to the STUDENTS table; part of it is a foreign key to the COURSES table.

• We have a foreign key in STUDENTS_COURSES that ensures each student in that table is also a student in STUDENTS. The same column in the foreign key is part of the primary key.

• We have a foreign key in STUDENTS_COURSES that ensures each course in that table is also a course in COURSES. The same column in this foreign key is part of the primary key.

When we apply these rules in the form of constraints, we will have three CREATE TABLE statements, three PRIMARY KEYS, and two FOREIGN KEY constraints.

The table description for the COURSES table is shown here:

 Name                           Null?    Type ------------------------------ -------- ------------ COURSE_NAME                             VARCHAR2(10) COURSE_DESC                             VARCHAR2(20) NO_OF_CREDITS                           NUMBER(2,1); 

The table description for the STUDENTS_COURSES table is shown here:

 Name                           Null?    Type ------------------------------ -------- ------------ STUDENT_ID                              VARCHAR2(10) COURSE_NAME                             VARCHAR2(10) 

The columns of the cross-reference table contain columns that must reference the parents, in this case, STUDENTS and COURSES. This is first step. Additional columns can be added to this table, such as the professor who teaches the class and when it is taught. A cross-reference table can be just the joining columns. It can also contain additional attributes.

The DDL for this many-to-many relationship is show in Script 3-3.

Script 3-3 Many-to-Many Relationship.

 CREATE TABLE students  (student_id    VARCHAR2(10) NOT NULL,   student_name  VARCHAR2(30) NOT NULL,   college_major VARCHAR2(15) NOT NULL,   status        VARCHAR2(20) NOT NULL,   state         VARCHAR2(2),   license_no    VARCHAR2(30)) TABLESPACE student_data; CREATE TABLE courses  (course_name   VARCHAR2(10) NOT NULL,   course_desc   VARCHAR2(20) NOT NULL,   no_of_credits NUMBER(2,1)  NOT NULL) TABLESPACE student_data; CREATE TABLE students_courses  (student_id    VARCHAR2(10) NOT NULL,   course_name   VARCHAR2(10) NOT NULL) TABLESPACE student_data; ALTER TABLE students   ADD CONSTRAINT pk_students   PRIMARY KEY (student_id)   USING INDEX TABLESPACE student_index; ALTER TABLE courses   ADD CONSTRAINT pk_courses   PRIMARY KEY (course_name)   USING INDEX TABLESPACE student_index; ALTER TABLE students_courses   ADD CONSTRAINT pk_students_courses   PRIMARY KEY (student_id, course_name)   USING INDEX TABLESPACE student_index; ALTER TABLE students_courses   ADD CONSTRAINT fk_students_courses_st_id   FOREIGN KEY (student_id)   REFERENCES students (student_id); ALTER TABLE students_courses   ADD CONSTRAINT fk_students_courses_course   FOREIGN KEY (course_name)   REFERENCES courses (course_name); 

3.3.8 Self-Referential Integrity

Self-referential integrity is common in many applications. Self-referential integrity allows parent-child relationships to exist between instances of the same entity. For example, all professors are in the following PROFESSORS table, including the individual department heads.

Some professors have a department head. These department heads are also professors. The following creates a table and a primary key, and then establishes a self-referential integrity constraint.

 CREATE TABLE professors  (prof_name     VARCHAR2(10) NOT NULL,   specialty     VARCHAR2(20) NOT NULL,   hire_date     DATE         NOT NULL,   salary        NUMBER(5)    NOT NULL,   dept_head     VARCHAR2(10)) TABLESPACE student_data; ALTER TABLE professors   ADD CONSTRAINT pk_professors   PRIMARY KEY (prof_name)   USING INDEX TABLESPACE student_index; ALTER TABLE professors   ADD CONSTRAINT fk_professors_prof_name   FOREIGN KEY (dept_head)   REFERENCES professors (prof_name); 

This permits a scenario where a professor MAY have a department head (DEPT_HEAD) and if they have a department head, that column value MUST be an existing PROF_NAME. Figure 3-7 shows, for example, that Blake is Milton's department head.

Figure 3-7. Self-Referential Integrity Data.

Self-Referential Integrity, for the aforementioned data, is one level deep. The foreign key definition permits unlimited nesting. Multilevel nesting is illustrated with the following example. We create a sample table TEMP with three columns: WORKER, SALARY, and MANAGER. A worker may or may not have a manager. A manager must first be inserted as a worker. Workers can be managers of other workers who may manage other workers. The relationships among these workers and their salaries is shown as well.

 CREATE TABLE TEMP    (worker   VARCHAR2(10) PRIMARY KEY,     salary   NUMBER(3),     manager  VARCHAR2(10) REFERENCES TEMP (worker)); 

Allen manages Bill and Beth. Beth manages Cindy and Carl. Carl manages Dean and Dave.

 Allen (salary=10) manages: Bill (salary=10), Beth (salary=10) Beth  (salary=10) manages: Cindy (salary=5), Carl (salary=5) Carl  (salary=5)  manages: Dean  (salary=5), Dave (salary=5) 

This data contains multilevel relationships forming a logical tree organization. A SELECT statement using a CONNECT BY and START AT clause enables a query to return, for example, the sum of all salaries starting at a specific point in the tree. The inserts for this data are:

 INSERT INTO TEMP values ('Allen',  10,  null); INSERT INTO TEMP values ('Bill' ,  10, 'Allen'); INSERT INTO TEMP values ('Beth' ,  10, 'Allen'); INSERT INTO TEMP values ('Cindy',  5,  'Beth'); INSERT INTO TEMP values ('Carl' ,  5,  'Beth'); INSERT INTO TEMP values ('Dean' ,  5,  'Carl'); INSERT INTO TEMP values ('Dave' ,  5,  'Carl'); 

The following is a START AT SELECT statement to return Beth's salary including all those who work for Beth. This is the sum of salaries for Beth, Cindy, and Carl, plus Dean and Dave who are managed by Carl: the sum is $30.00.

  SQL>  SELECT sum(salary)  2  FROM temp  3  START WITH worker='Beth'  4  CONNECT BY PRIOR worker=manager;  SUM(SALARY)   -----------   30  

3.3.9 PL/SQL Error Handling with Parent/Child Tables

Foreign key constraint errors are captured with mapping an exception to the Oracle error, minus 2291. The following procedure inserts a student row and captures the duplicate inserts. In this case a duplicate could be a primary key constraint violation or a unique constraint error. Both generate the DUP_VAL_ON_INDEX exception.

The DDL in Script 3-1 declares a CHECK constraint on STATE and LICENSE NO. The following procedure inserts a student and captures duplicate inserts that may violate the primary key or unique constraint, foreign key constraint, and check constraint.

 CREATE OR REPLACE PROCEDURE     insert_student(v_student_id VARCHAR2,         v_student_name VARCHAR2,         v_college_major VARCHAR2, v_status VARCHAR2,         v_state VARCHAR2, v_license_no VARCHAR2) IS     check_constraint_violation exception;     pragma exception_init(check_constraint_violation, -2290);     foreign_key_violation exception;     pragma exception_init(foreign_key_violation, -2291); BEGIN     INSERT INTO students VALUES         (v_student_id, v_student_name,          v_college_major, v_status,          v_state, v_license_no);     dbms_output.put_line('insert complete'); EXCEPTION     WHEN DUP_VAL_ON_INDEX THEN         dbms_output.put_line('PK or unique const violation');     WHEN check_constraint_violation THEN         dbms_output.put_line('check constraint violation');     WHEN foreign_key_violation THEN         dbms_output.put_line('foreign key violation'); END; 

3.3.10 The Deferrable Option

In this section we use two tables that store generic parent/child data. The data model for this is shown in Figure 3-8.

Figure 3-8. Parent-Child Relationship.

The PARENT table description is:

 Name                           Null?    Type ------------------------------ -------- ------------ PARENT_NAME                             VARCHAR2(2) PARENT_DESC                             VARCHAR2(10) 

The CHILD table description is:

 Name                           Null?    Type ------------------------------ -------- ------------ CHILD_NAME                              VARCHAR2(2) PARENT_NAME                             VARCHAR2(10) 

The DDL, shown next, includes a DEFERRABLE option on the foreign key constraint.

 CREATE TABLE parent  (parent_name  VARCHAR2(2) CONSTRAINT pk_parent PRIMARY   KEY, parent_desc  VARCHAR2(10)); CREATE TABLE child  (child_name  VARCHAR2(10),   parent_name VARCHAR2(2)); ALTER TABLE child ADD CONSTRAINT fk_child_parent_name   FOREIGN KEY (parent_name)   REFERENCES parent (parent_name) DEFERRABLE; 

This DEFERRABLE attribute means that we can choose to defer the constraint and load a set of data into the parent and child tables, without regard to referential integrity, under the assumption that when the load completes, the data will be clean. Then a commit will automatically apply the rule on our loaded data. We can load 10 records into a child table that has no parents and then load the parents. Validation occurs on the commit. If the data we loaded violates the referential integrity rule, the transaction is rolled back.

You can do this in SQL*Plus with the following.

 SET constraints ALL DEFERRED; INSERT INTO child VALUES ('child_1','P1'); INSERT INTO child VALUES ('child_2','P1'); INSERT INTO child VALUES ('child_3','P2'); INSERT INTO child VALUES ('child_4','P3'); INSERT INTO child VALUES ('P1','a parent'); INSERT INTO child VALUES ('P2','a parent'); INSERT INTO child VALUES ('P3','a parent'); COMMIT; 

You can use this functionality in a stored procedure with the following:

 CREATE OR REPLACE PROCEDURE P IS BEGIN     EXECUTE IMMEDIATE 'SET constraints ALL DEFERRED';     INSERT INTO child VALUES ('child_1','P1');     INSERT INTO child VALUES ('child_2','P1');     INSERT INTO child VALUES ('child_3','P2');     INSERT INTO child VALUES ('child_4','P4');     INSERT INTO child VALUES ('P1','a parent');     INSERT INTO child VALUES ('P2','a parent');     INSERT INTO child VALUES ('P3','a parent');     COMMIT; END P; 

The general motivation for this is that the data comes in an inconsistent order. The aforementioned procedure inserts all child rows, then the parents. The aforementioned inserts contain hard-coded literal values. A more realistic situation could involve records read from a file using the UTL_FILE package. The input file would contain many rows out of order; that is, all child records followed by all parent records. In this case a procedure could contain a simple loop, iterate over all child inserts followed by all parent inserts, and then perform integrity checking upon a commit. In very select situations this can be a reasonable approach.

In general, there are other tools available to obviate the need for this option.

• You can write an exception handler around each child insert and should that fail, because there is no parent, you write code in the exception handler to insert a parent. Following the parent insert, you insert the child. For example, a child insert would be enclosed within an exception handler block similar to the following block, which maps the foreign key constraint violation (ORA-02291) to an exception. Upon an exception, insert a parent and then the child.

   DECLARE     no_parent EXCEPTION;     PRAGMA EXCEPTION_INIT (no_parent, -2291);     new_parent VARCHAR2 (2) := 'P6';     new_child VARCHAR2(10) := 'child_5'; BEGIN     INSERT INTO child VALUES (new_child,          new_parent); EXCEPTION     WHEN no_parent THEN         INSERT INTO parent VALUES (new_parent,              'no desc');         INSERT INTO child VALUES (new_child,              new_parent); END; 

• You can store failed child records in a PL/SQL table and when you complete inserting all parents, you go back and load the child records saved in the PL/SQL table. The PL/SQL table would be derived from the child table with the following syntax:

   TYPE temporary_table_type IS TABLE OF     CHILD%ROWTYPE      INDEX BY BINARY_INTEGER; temporary_table temporary_table_type; 

• Another option is to use a temporary table to store failed child records. This is a solution similar to using a PL/SQL Table. The PL/SQL table is manipulated like an array. The temporary table is accessed through SQL. With this solution, you load child records into this temporary repository, load all parents, then load from the temporary table into the final child table. The SQL for a temporary table is:

   CREATE GLOBAL TEMPORARY TABLE CHILD_TEMP  (child_name  VARCHAR2(10),   parent_name VARCHAR2(2))ON COMMIT DELETE ROWS; 

The deferrable option is a powerful tool but should not be used as a standard practice because it disables the very rules that are strongly encouraged ”even if the deferred state is a temporary one.

The SQL*Plus script and aforementioned stored procedure set the constraint to a deferred state with the statement:

 SET CONSTRAINT ALL DEFERRED; 

This SET command is one option for deferring the constraint. The other option is to replace ALL with the specific constraint name ”that would be FK_CHILD_PARENT_NAME. This means your application code specifically references a constraint name. The SET CONSTRAINT ALL only affects your current transaction; the code is more generic because it does not specifically reference a constraint name. As a matter of style and preference, specific mentioning of constraint names in the code is not a recommendation.

When we create a constraint, using the following DDL, it means that we have the option to write code, or use SQL*Plus, to temporarily defer that constraint. Unless we DEFER the constraint, everything remains the same. But, if we choose to write code that temporarily breaks the rule, we must first DEFER the constraint.

 ALTER TABLE child ADD CONSTRAINT fk_child   FOREIGN KEY (parent_name)   REFERENCES parent (parent_name) DEFERRABLE; 

An option to this is to initially create the constraint in a deferred state. The DDL for this is the following.

 ALTER TABLE child ADD CONSTRAINT fk_child   FOREIGN KEY (parent_name)   REFERENCES parent (parent_name) DEFERRABLE   INITIALLY DEFERRED; 

With this option everything is reversed . When we load data into the child and then parent tables, the constraint is deferred ”this is the same as not having a constraint. Should we want to write code with the constraint enforced, as we perform each insert, then we would precede those insert statements with:

 SET CONSTRAINT ALL IMMEDIATE;