aspose file tools*
The moose likes Oracle/OAS and the fly likes Oracle Forign Key Basic . Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle Forign Key Basic ." Watch "Oracle Forign Key Basic ." New topic
Author

Oracle Forign Key Basic .

Abhijeet Deshmukh
Greenhorn

Joined: Aug 09, 2004
Posts: 6
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

Joined: Apr 14, 2004
Posts: 10336

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 ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Reza Ravasizadeh
Ranch Hand

Joined: Jun 08, 2004
Posts: 177
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.


Reza
Santosh Pasupuleti
Ranch Hand

Joined: Aug 10, 2005
Posts: 97
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

Joined: Aug 10, 2005
Posts: 97
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

Joined: Aug 10, 2005
Posts: 97
Looks like multiple spaces are collapsed into one single space.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Oracle Forign Key Basic .