0

how do you change the relationship between two tables to a many to many to many relationship in sql. Im using oracle for the DB.

thanks

clonebaby59
  • 177
  • 1
  • 12
  • You're going to need to provide much more information about what the content of these two tables are in order for someone to provide an answer. – Alan Barber Nov 15 '11 at 14:29

1 Answers1

3

Relationships among tables are almost always, ONE-TO-MANY, or ONE-TO-ONE. There is no MANY-TWO-MANY relationship between two tables. If you want a MANY-TO-MANY you will need to create intermediate relation to hold the relationship.

For example, if you want a MANY-TO-MANY relationship between table A and B you will need to create an intermediate table C:

create table a (a_id number primary key);
create table b (b_id number primary key);
-- c will hold many-to-many relationship between a and b
create table c (
    a_id number not null references a(a_id),
    b_id number not null references b(b_id),
    primary key(a_id, b_id)
);
Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • +1 Table `c` can have many different names; Mapping table, a junction table, a relationship table, etc, etc. There's even a question on SO just about its naming: http://stackoverflow.com/questions/3045034/whats-the-correct-name-for-a-association-table-many-to-many-relationship – MatBailie Nov 15 '11 at 14:38
  • "There is no MANY-TWO-MANY relationship between two tables" -- Can you explain what you mean please? As read, it appears to be a misstatement. I could post schema modelling a many-to-many relationship using one, two or three tables. – onedaywhen Nov 16 '11 at 11:41
  • @onedaywhen: You can have MANY-TO-MANY relationships modeled with three or more tables. I don't think you can have one with one or two tables... – Pablo Santa Cruz Nov 16 '11 at 11:47