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.
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 ]
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.
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.