Q
Problem solve Get help with specific problems with your technologies, process and projects.

Two tables with same value as primary key

Can any two tables having the same column value as primary key refer to the other table by defining its primary key as a foreign key?

Can any two tables having the same column value as primary key refer to the other table by defining its primary key as a foreign key?
Any table can refer to another table's primary key with a foreign key constraint. But the big question is can you have a circular reference between two tables, in effect, each table becoming the child to the other. Let's look at a simple example. First, we'll create two tables:
SQL> create table t1 (
  2  id number primary key,
  3  val varchar2(10));

Table created.

 SQL> create table t2 (
  2  id number primary key,
  3  val varchar2(10));

Table created.
Next, we'll create an FK constraint from T1 to T2.
SQL> alter table t1 add constraint t1_t2_fk
  2  foreign key (id) references t2;

Table altered.
Now, we'll attempt to create a constraint from T2 to T1 using the same columns.
 SQL> alter table t2 add constraint t2_t1_fk
  2  foreign key (id) references t1;

Table altered.
So we now have created the constraints successfully. So Oracle will allow this situation, but there is a problem we have introduced here. I cannot insert values into any of the two tables:
 SQL> insert into t1 values (1,'test');
insert into t1 values (1,'test')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.T1_T2_FK) violated - parent key
not found
As you can see, I could not insert a value into T1 because the parent record does not exist in T2. Similarly, I cannot insert a value into T2 because the parent record does not exist in T1. To get around this problem, you will have to disable the constraint(s) when inserting data.

That being said, I cannot think of a relation that would use this type of referential integrity. How can a table...

require a parent record in another table, but that other table requires a parent record in the first table? This is a circular definition and probably means the relational model is flawed somewhere.

Dig Deeper on Oracle database design and architecture

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close