SQL Information Schema - reading COMPOSITE foreign keys

I'm working on a generic application that uses a set of pre-selected database tables (and it's relationships) to generate a user interface for managing data in those tables. Basicaly, I'm just looking up stuff from the information schema, getting all the primary and foreign keys and generating input fields and links to other dialogs based on that. Because it's supposed to be a generic solution, it should work with any set of tables that do have a meaningful relationship among each other.

One of the things I ran into are foreign keys, in particular - COMPOSITE foreign keys under one constraint. For example, if I have the following tables:

Company

CompanyID, CompanyName, CompanyID
--companyID is the primary key which identifies the company. 

Division

DivisionID, CompanyID, DivisionName, DivisionID+CompanyID
--DivisionID+CompanyID is a composite primary key for a division, because it's a one-to-man开发者_运维知识库y relationship and division is DEPENDENT on Company. 

Team

TeamID, DivisionID, CompanyID, TeamName, TeamID+DivisionID+CompanyID
--same as above - a Team is dependent on Division, which has a composite primary key.

Now, this database model is - by all definitions - a valid model (and SQL Server allows it) - but I've come across a certain problem.

For example, in the information schema, both DivisionID and CompanyID are 'assigned' to the same CONSTRAINT. So, when I join the right tables, I get a problem. There's no way of knowing which column in one table is which column in the other. In the above example, the column names are the same (CompanyID is the name of every column that relates to the ID of the company - be it in the COMPANY table or the TEAM table, etc...) but, there's no rule that says the name of the columns must be the same, so I am at a loss on how to actually make the program know which column is which.

TABLE1    COLUMN1        CONSTRAINT         TABLE2     COLUMN2
TEAM      CompID      TEAM_HAS_DIVISION    DIVISION   CompanyID
TEAM      DivID       TEAM_HAS_DIVISION    DIVISION   DivisionID

Is there a way for the computer to know that CompID in Table TEAM references CompanyID, and not DivisionID in the DIVISION table?

I couldn't find any way to do this from the data in INFORMATION_SCHEMA views.

Yes, a human can easily figure out that CompID=CompanyID, etc... but as I mentioned previously - I'm trying to make a generic solution that wouldn't require a person to look at this and make decisions and mistakes :=)

---

You have to match them up by column order.

• REFERENTIAL_CONSTRAINTS tells you that TEAM_HAS_DIVISION constraint has PK_Division as your primary key
• KEY_COLUMN_USAGE tells you that TEAM_HAS_DIVISION has DivID and CompID in that order, specified by ORDINAL_POSITION column
• KEY_COLUMN_USAGE also tells you that PK_Division has DivisionID and CompanyID in that order, specified by ORDINAL_POSITION column

This is how you know which one refers to which.