0

Using "List of foreign keys and the tables they reference", I am able to get the name of the join table and a table it joins with, but not the name of the third table. How do I get all three tables/fields involved in a many-to-many?

For example, how do I get the following (note the r2_* on the second row):

TableName   Field       r_TableName r_Field r2_TableName    r2_Field
Users       VehicleId   Vehicles    Id      NULL            NULL
UserGroups  UserId      Groups      Id      Users           Id
Cats        UserId      Users       Id      NULL            NULL

Given the following tables:

CREATE TABLE Users(
    Id NUMBER(10,0),
    UserName NVARCHAR2(20),
    VehicleId NUMBER(10,0),
    CONSTRAINT user_pk PRIMARY KEY(ID)
);
ALTER TABLE Users ADD CONSTRAINT
    users__vehicle_fk FOREIGN KEY(VehicleId) REFERENCES Vehicles(Id) ENABLE;
CREATE TABLE Vehicles(
    ID NUMBER(10,0),
    VehicleName NVARCHAR2(20),
    CONSTRAINT vehicle_pk PRIMARY KEY(ID)
);
CREATE TABLE Cats(
    ID NUMBER(10,0),
    CatName NVARCHAR2(20),
    UserId NUMBER(10,0),
    CONSTRAINT cat_pk PRIMARY KEY(ID)
);
ALTER TABLE Cats ADD CONSTRAINT
    cat__user_fk FOREIGN KEY(UserId) REFERENCES Users(ID) ENABLE;   
CREATE TABLE Groups(
    Id NUMBER(10,0),
    GroupName NVARCHAR2(20),
    CONSTRAINT group_pk PRIMARY KEY(ID)
);  
CREATE TABLE UserGroups (
    UserId NUMBER(10,0),
    GroupId NUMBER(10,0),
    CONSTRAINT user__groups_pk PRIMARY KEY(UserId, GroupId)
);  
ALTER TABLE UserGroups ADD CONSTRAINT
    userGroups__user_fk FOREIGN KEY(UserId) REFERENCES Users(Id) ENABLE;
ALTER TABLE UserGroups ADD CONSTRAINT
    userGroups__group_fk FOREIGN KEY(GroupId) REFERENCES GROUPS(ID) ENABLE;

I've been working with variations of:

SELECT ac1.OWNER, ac1.CONSTRAINT_NAME, ac1.CONSTRAINT_TYPE, ac1.TABLE_NAME,
    ac1.R_OWNER, ac1.R_CONSTRAINT_NAME, '@@@@@@@@@@'
    , ac2.OWNER, ac2.CONSTRAINT_NAME, ac2.CONSTRAINT_TYPE, ac2.TABLE_NAME,
    ac2.R_OWNER, ac2.R_CONSTRAINT_NAME, '@@@@@@@@@@'
    ,conscols.*, '@@@@@@@@@@'
    ,r_conscols.*
FROM all_constraints ac1
JOIN all_constraints ac2
    ON ac1.r_constraint_name = ac2.constraint_name
    AND ac1.owner = ac2.owner
JOIN all_cons_columns conscols
    ON ac1.owner = conscols.owner
    AND  ac1.CONSTRAINT_NAME = conscols.constraint_name
JOIN all_cons_columns r_conscols
    ON ac2.owner = r_conscols.owner
    AND  ac2.CONSTRAINT_NAME = r_conscols.constraint_name
    AND r_conscols.POSITION = conscols.POSITION

I can see the raw data for indirect relationships (like the second of three rows in my sample output), but I can see no clear way to join that data with direct relationships so that I know that they all involve the same M:M join.

Note: Relationships that go deeper than a many:many using a single join table need not be considered.

How do I accomplish getting this data together?

Community
  • 1
  • 1
Charles Burns
  • 10,310
  • 7
  • 64
  • 81

1 Answers1

0

I am not 100% certain this is what you want, but did you intend to use outer joins instead of inner joins?

SELECT ac1.owner
     , ac1.constraint_name
     , ac1.constraint_type
     , ac1.table_name
     , ac1.r_owner
     , ac1.r_constraint_name
     , '@@@@@@@@@@'
     , ac2.owner
     , ac2.constraint_name
     , ac2.constraint_type
     , ac2.table_name
     , ac2.r_owner
     , ac2.r_constraint_name
     , '@@@@@@@@@@'
     , conscols.*
     , '@@@@@@@@@@'
     , r_conscols.*
  FROM user_constraints ac1
       LEFT OUTER JOIN user_constraints ac2
           ON ac1.r_constraint_name = ac2.constraint_name
          AND ac1.owner = ac2.owner
       LEFT OUTER JOIN user_cons_columns conscols
           ON ac1.owner = conscols.owner
          AND ac1.constraint_name = conscols.constraint_name
       LEFT OUTER JOIN user_cons_columns r_conscols
           ON ac2.owner = r_conscols.owner
          AND ac2.constraint_name = r_conscols.constraint_name
          AND r_conscols.position = conscols.position
Brian Leach
  • 2,025
  • 1
  • 11
  • 14