insert a row into a table having 2 columns only

by Himanshu
(Delhi)

hi guys,
I want to seek your help. I have two tables- table1 and table2 namely having columns as col1 and col2 in both the tables

table1 table2
col1(pk) col2(pk)
col2(fk) col1(fk)
both referencing each other.
my question is how to insert a row in table1????

What you have described is rather an artificial situation but before we go into that let's summarise the situation and then answer the question.

You have 2 tables in your Oracle, with each table referencing the other which means that each table has a foreign key constraint referencing the primary key of the other.

On paper, this situation would seem to mean that you can't insert any data into either table because it would violate the referential integrity (foreign key) constraint and generate an ORA-02291: integrity constraint violated - parent key not found error.

So what can you do about it? Well of course, one obvious solution is to drop or disable one of the foreign key constraints but this would allow "orphan" records to be inserted into your table - precisely the situation you're trying to avoid!

Fortunately, Oracle provides another solution (although probably not with this scenario in mind): constraint checking (validation) can be deferred until the end of the transaction (i.e. until the changes are committed). This means you can insert an "orphan" record into one table and the insert the parent record (which is also the child of the first record) into the other table without generating a constraint violation error. However If the foreign keys are not valid when the changes are committed then Oracle generates a constraint violation error at that point and the changes made in that transaction are rolled back.

To be able to defer constraint checking, the constraint must have been created as DEFERRABLE and either set to INITIALLY DEFERRED or deferred by use of the SET CONSTRAINT statement.

Having addressed the technicalities of deferring constraint checking, let's look at the wider issue of having 2 tables dependent on each other. To us, this seems to be a situation that should never occur in the real world. If you have 2 tables dependent on each other in your Oracle database then you should review your system design and change your data model. Just because something is possible doesn't mean it's a good idea.

Let's look at a real-world example with the ubiquitous emp and dept representing employees and departments respectively. In this situation, the emp table table definition would be like so:

CREATE TABLE emp
(emp_id number not null primary key
, dept_id number);

N.B. We can't define the foreign key constraint yet because dept doesn't exist. Dept would look like this:
CREATE TABLE dept
(dept_id number not null primary key
, emp_id number not null references emp(emp_id));

Now we can define the foreign key (referential integrity constraint) on emp.
ALTER TABLE emp ADD (CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(dept_id));

So, we have the table definitions with each table referencing the other. If we look at the table definitions however (ignoring constraints) we can see that they are exactly the same which means we only need one of them in our Oracle database!

You could argue that that is being completely unrealistic - both emp and dept would have more than 2 columns in the real world - so let's extend our example and add extra columns such as dept_name and emp_name to make it more realistic. In which case the table definitions would be like so:
CREATE TABLE emp (emp_id number not null primary key
,emp_name varchar2(120)
, dept_id number);

CREATE TABLE dept (dept_id number not null primary key
, dept_name varchar2(30)
, emp_id number not null references emp(emp_id));

ALTER TABLE emp ADD (CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(dept_id));

Now let's consider what happens when one of our employees changes the department he or she works for, say from Marketing to HR. Again we have to defer constraint checking until we commit to ensure Oracle doesn't generate a constraint violation error but we also need to update 2 records - one in the emp table and one in the dept table.

It gets worse though. Suppose we have a reorganisation and we change the name of one of our departments. Due to the fact that the dept table has one record for each employee we need to update all those records - a lot of unnecessary work. Also every time a new employee starts or someone leaves we have to update 2 tables again.

All this means a lot of extra work for our Oracle database and points to a major design flaw. In other words if you ever find yourself with 2 (or more) tables that are co-dependent, you've made a mistake and need to re-analyse the business processes and data model.

More information on creating, modifying and using constraints can be found in the Oracle SQL Language Reference manual.