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?

    Requires Free Membership to View

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 first published in February 2013

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: