posted 18 years ago
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.