SQL code to check for Many-To-Many relationships

Question

I have been researching non-stop and have only managed to find out - how to check for a 1-To-Many relationship in SQL. I want to know if anyone can help me find out on how to check for a Many-To-Many relationship in SQL code. The tables in the database are in correct normal form.

The scenario I have is:

• A user will select three tables from the database.
• Once the three tables have been selected, I need to check if those tables have a Many-To-Many relationship.
• So I need to check if the three tables that the user has selected are associated with a primary key and the intersection table has the two reference keys that point to the two other tables.

SELECT K_Table = FK.TABLE_NAME,
       FK_Column = CU.COLUMN_NAME,
       PK_Table = PK.TABLE_NAME,
       PK_Column = PT.COLUMN_NAME,
       Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
INNER JOIN (SELECT i1.TABLE_NAME, 
                   i2.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
                    ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT 
        ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME = '" + ActionM2M.Table2 + "'  AND 
      FK.TABLE_NAME = '" + ActionM2M.ITableNames1 + "' AND 
      exists (SELECT K_Table = FK.TABLE_NAME,
                     FK_Column = CU.COLUMN_NAME,
                     PK_Table = PK.TABLE_NAME,
                     PK_Column = PT.COLUMN_NAME,
                     Constraint_Name = C.CONSTRAINT_NAME
              FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
              INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
                      ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
              INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
                      ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
              INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
                      ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
              INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME
                          FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                          INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
                                  ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                          WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT
                      ON PT.TABLE_NAME = PK.TABLE_NAME
              WHERE PK.TABLE_NAME = '" + ActionM2M.Table1 + "'  AND 
                    FK.TABLE_NAME = '" + ActionM2M.ITableNames1 + "')

Answer 1

I'm not sure that making the user select three tables like that is a good UI design, but we'll put that aside for the time being.

You've not indicated what language you're coding this in, or the DBMS you're using. Thus, specific details are going to be missing. However:

• There are ways to find out the primary key of each table. This might be an ODBC or JDBC method, or you might query on the the SQL/Schema tables, or you might query the system catalog directly.
• Similarly, there are ways to find out the foreign keys for each table, including the referencing columns and the referenced tables.
• If the three tables are chosen in a random sequence, then you have to look through the primary keys of each table to find one which is composite and which contains columns from two foreign keys. You then check that the other two tables are the ones referenced by the foreign keys.
• Ideally, though, you'd know that the first two selected tables are supposed to be the referenced table and the third is supposed to be the 'intersection' or 'junction' or 'association' table, so you don't have to find out which table has the 'junction' role; you just check that the table designated as having the 'junction' role does in fact meet the requirements.

I think that you might do better to present the linking information to the user, rather than forcing them into choosing the tables - but it does depend whether your 'users' are themselves DBAs or whether they're from HR or finance and have no clue about DBMS and tables in the first place.