Joey_hunny123
asked on
Oracle many-to-many
Many-to-many in Oracle.
It appears as though I am over complicating things. I have an ER diagram with a many-to-many link. I'm using Oracle as an Object-Relational-DBMS. I know I have to break the many-to-many relationship into two one-to-many relationships with an intermediate table. The intermediate table then contains two columns - primary key from table A and primary key from table B (I believe).
However, I cannot work out how to get relationships between this new table (C) and the two original tables (A and B). Are there any required?? I would have thought so, however http://www.oracle.com/technology/products/ias/toplink/doc/10131/main/_html/relmapun008.htm seems to imply that you just create this new table and insert the keys.
Is this so? I have been trying the following:
CREATE TYPE a AS OBJECT (
aKey integer,
...
aBtable relatesTo_list
);
/
CREATE TYPE b AS OBJECT (
bKey integer,
...
bAtable relatesTo_list
);
/
where 'relatesTo_list is a table of REF c' and c is:
CREATE TYPE c AS OBJECT (
aTable REF a,
bTable REF b
);
/
Obviously this is incorrect as I cannot get it anywhere close to working. I can create tables and have it compile but I cannot insert any data (infinite loop errors).
So... I assume it is easiest to answer with an example rather than trying to correct my nonsense above, but HOW DO YOU DO THIS!? Please! It really can't be hard!
Thanks,
J
It appears as though I am over complicating things. I have an ER diagram with a many-to-many link. I'm using Oracle as an Object-Relational-DBMS. I know I have to break the many-to-many relationship into two one-to-many relationships with an intermediate table. The intermediate table then contains two columns - primary key from table A and primary key from table B (I believe).
However, I cannot work out how to get relationships between this new table (C) and the two original tables (A and B). Are there any required?? I would have thought so, however http://www.oracle.com/technology/products/ias/toplink/doc/10131/main/_html/relmapun008.htm seems to imply that you just create this new table and insert the keys.
Is this so? I have been trying the following:
CREATE TYPE a AS OBJECT (
aKey integer,
...
aBtable relatesTo_list
);
/
CREATE TYPE b AS OBJECT (
bKey integer,
...
bAtable relatesTo_list
);
/
where 'relatesTo_list is a table of REF c' and c is:
CREATE TYPE c AS OBJECT (
aTable REF a,
bTable REF b
);
/
Obviously this is incorrect as I cannot get it anywhere close to working. I can create tables and have it compile but I cannot insert any data (infinite loop errors).
So... I assume it is easiest to answer with an example rather than trying to correct my nonsense above, but HOW DO YOU DO THIS!? Please! It really can't be hard!
Thanks,
J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.