Q

Managing SQL parent table-child table relations

Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.

I have a parent table (EMPLOYEE) and a child table (EMPLOYEE_ADDRESS). In CA ERwin Data Modeler, [and] the parent...

table-child table relationship is modeled as one-to-one or more. I want to ensure that for every record in the EMPLOYEE table, there exists at least one record in the EMPLOYEE_ADDRESS child table. How do I achieve this?

Normally, EMPLOYEE_ADDRESS would be a child table to the parent EMPLOYEE. That means an EMPLOYEE_ADDRESS child record must have an EMPLOYEE parent record. To implement that parent table-child table relationship, you would define a foreign key constraint from the EMPLOYEE_ADDRESS table to the EMPLOYEE table similar to the following:

SQL> create table employee (  
2  emp_id number,  
3  emp_name varchar2(50),  
4  address_id number);

Table created.

SQL> alter table employee add constraint employee_pk primary key (emp_id);

Table altered.

SQL> create table employee_address (  
2  address_id number,  
3  emp_id number,  
4  street varchar2(100),  
5  city varchar2(50),  
6  state char(2),  
7  zip varchar2(10));

Table created.

SQL> alter table employee_address add constraint employee_address_pk primary key (address_id);

Table altered.

SQL> alter table employee_address add constraint emp_addr_fk foreign key (emp_id)  
2  references employee;

Table altered.

In the above, I will get an error if I try to insert a record into the EMPLOYEE_ADDRESS table without a parent record in the EMPLOYEE table. The error would look like this:

SQL> insert into employee_address values (10,101,'101 Main Street','Anywhere','US','10101-0101');
insert into employee_address values (10,101,'101 Main Street','Anywhere','US','10101-0101')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.EMP_ADDR_FK) violated - parent key not found

But you seem to be asking about the other direction as well, making the parent table the child table and vice versa. So both tables are parents to each other. You can do this with another foreign key constraint:

SQL> alter table employee add constraint emp_fk foreign key (address_id)  
2  references employee_address;

Table altered.

So now, a conundrum or a chicken-and-egg scenario has been created. I can't add a record to either table without hitting a constraint violation:

SQL> insert into employee values (101,'Bob Smith',10);
insert into employee values (101,'Bob Smith',10)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.EMP_FK) violated - parent key not found

SQL> insert into employee_address  
2  values (10,101,'101 Main Street','Anywhere','US','10101-0101');
insert into employee_address
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.EMP_ADDR_FK) violated - parent key not found

The solution is to defer the checking of the constraints until commit time. We will need to slightly modify the foreign key constraints to defer constraint enforcement until commit.

SQL> alter table employee drop constraint emp_fk;

Table altered.

SQL> alter table employee add constraint emp_fk foreign key (address_id)  
2  references employee_address initially deferred deferrable;

Table altered.

SQL> alter table employee_address drop constraint emp_addr_fk;

Table altered.

SQL> alter table employee_address add constraint emp_addr_fk foreign key (emp_id)  
2  references employee initially deferred deferrable;

Table altered.

Now we can do the INSERTs as desired:

SQL> insert into employee values (101,'Bob Smith',10);

1 row created.

SQL> insert into employee_address  
2  values (10,101,'101 Main Street','Anywhere','US','10101-0101');

1 row created.

SQL> commit;

Commit complete.

And if we try to insert a record into the EMPLOYEE table without a corresponding address entry, we get an error on commit.

SQL> insert into employee values (102,'Jane Smith',20);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SYS.EMP_FK) violated - parent key not found

More on Oracle PL-SQL

Read all of Brian Peasland's expert answers

Ask our experts your own Oracle question

Keep in mind that deferred constraint checking may affect the application. The constraint is not checked until commit, so I did not receive the ORA-2291 error when I did the INSERT statement, as is usually the case. So, the application will have to handle the exception at a later point in time.

Lastly, I would really question the need for this. For most data models, the EMPLOYEE_ADDRESS table is a child record to the EMPLOYEE table, but not vice versa. It is possible to hire an employee and enter them into the system before their address is known. And what if two employees share the same address? The address record cannot have two parents, which means you will have identical addresses. Doing so would violate third normal form in your data model. There are good reasons to code this circular relationship, but just be sure that it's what you want to do, because it does deviate from something more normal.

This was last published in February 2013

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

>> I have a parent [sic] table (EMPLOYEE) and a child [sic] table (EMPLOYEE_ADDRESS). In CA ERwin Data Modeler, the parent [sic] table-child [sic] table relationship is modeled as one-to-one or more. I want to ensure that for every record [sic] in the EMPLOYEE table, there exists at least one record [sic] in the EMPLOYEE_ADDRESS child [sic] table. How do I achieve this? <<

1. Rows are not records. This is a fundamental concept.
2. You have no idea what ISO-11179 data element names should be.
3. A table is a set, so its name has to be plural or collective name. A magnetic tape programmer processes records one at time, so IDEF and early pre-RDBMS modeling techniques used singular names to show this sequential processing. The better name would be “Personnel”, a high level abstraction of a set.
4. The terms “parent [sic]” and “child [sic]” are not part of RDBMS; they came from network database. In data modeling we have string and weak entities (a weak entity exists because of a strong one). In SQL, we have referenced and referencing tables.
5. While I realize this is skeleton code, it is still fundamentally wrong. An emp_id is a tag number, so it has to be CHAR(n). What math are you doing with it? That is the only reason to make it NUMBER.
6. You never researched the USPS and CASS standards; your sizes are wrong!. No need to re-invent the wheel. A ZIP code is five digits; a street address is VARCHAR(35) in USPS standards. Etc.


Here is quick fix:

CREATE TABLE Personnel
(emp_id CHAR2(10) NOT NULL PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL,
address_id CHAR2(10) NOT NULL);

CREATE TABLE Personnel_addresseses
(address_id CHAR(10) NOT NULL PRIMARY KEY,
emp_id CHAR(10) NOT NULL
REFERENCES Personnel (emp_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
street_address VARCHAR2(35) NOT NULL,
city_name VARCHAR2(25) NOT NULL,
state_code CHAR(2) NOT NULL,
zip_code CHAR(5) NOT NULL);

This demonstrates simple DRI actions, but it is not a good example for circular references. You need two examples:

1. “You cannot get a job without experience and you cannot get experience without a job!” This is what you were trying to show, but you need two independent strong entities, not strong/weak pair.
2. You need a self-reference example. The best one is a table with (previous_date, start_date, end_date) columns where we check to see that for each row, (previous_date+1 = start_date), a “no gaps” constraint.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close