Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle Forign Key Basic .

 
Abhijeet Deshmukh
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

i have a query about forign key constaint. I am using oracle as backend for my project and have two tables having forign keys to each other on different columns.
When i was tring to insert any record it throws and error that parent key not found.
how do i resolve this problem.

Thanks & Regards,
Abhijit
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have what sounds like a circular relationship. Its best to try to model the data in such a way that this relationship disappears, for the reason you are seeing, i.e. if entity A can't exist till there is an entry in entity B, and entity B can't exist till there is an entry in entity A. Quite often a circular relationship is an indicator of a mistake in your data model.

That being said, you can work round a relationship like this by using a "default" row. You can add a row to both tables which is just a place holder to allow you to perform the initial join. You will have to add this row before applying the constraints to both tables. When you insert data into one table you make give it an association with the default row of the other (and vice versa) then update the rows to represent the correct relationship. However, this is very definitely a work around and not an ideal solution by any stretch of the imagination.

Perhaps you could post the DDL for both tables and someone may suggest a better way of modelling this?
[ December 19, 2005: Message edited by: Paul Sturrock ]
 
Reza Ravasizadeh
Ranch Hand
Posts: 177
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can't have many to many relationship. what you explain is many to many relationship.
Try to implement one to many relation ship (read some DB document about relational database design)
If two tabel have many to many relation ship I make them three tables with two one to many relation ship.
 
Santosh Pasupuleti
Ranch Hand
Posts: 97
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Abhijeet,

Lets assume that you have the following schema:
Employee table has the following columns
(Empid, EmpName, Deptid)
EmpId is the primary key and DeptId is the foreign key.


Department table has the following columns :
(Deptid, DeptName, DeptLocation)
DeptId is the primary key. Department table is considered as parent table and Employee table is considered as child table.

If we have to insert a row into Employee table then the value in the DeptId column for this new row should be already present in Department table.

For example :

EmpIdEmpNameDeptId
------------------
1Abhijeet10
2Reza20
3paul20

DeptIdDeptNameDeptLocation
--------------------------
10HRNew York, NY
20R&DReston, VA
30SalesNew York, NY


The following sql will be succesful:
sql > insert into employee (Empid, EmpName, Deptid) values (3, 'santosh', 30);


But the following sql will throw an error:
sql > insert into employee (Empid, EmpName, Deptid) values (3, 'santosh', 40);

Since the value of DeptId (which is 40) is not found in the range (10, 20, 30), the above operation will result into an err.


Hope this helps.

P.S : Relational Databases (Oracle, DB2, SQL server) do not allow circular foreign keys or many to many relationships. You will hav to break a many to many relationship into two diferent One-To-Many and Many-to-One realtionships.
 
Santosh Pasupuleti
Ranch Hand
Posts: 97
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please read the contents of the tables as below:

EmpId EmpName DeptId
----- ------- ------
1 Abhijeet 10
2 Reza 20
3 paul 20

DeptId DeptName DeptLocation
------ -------- ------------
10 HR New York, NY
20 R&D Reston, VA
30 Sales New York, NY
 
Santosh Pasupuleti
Ranch Hand
Posts: 97
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Looks like multiple spaces are collapsed into one single space.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic