I use this table sys.Foreign_Key_Columns to find my foreign keys. Is it possible to identify if the relationship is a 1-1, 1-Many, or Many-Many? And of course, if it is a 1-Many, which table is which?
Also, how do I find if there are compound keys in a relationship join?
You can identity if the relationship is 1-1 or 2+-2+ using constraint_column_id. It can't be 1-many, as that's not a valid FK construct.
If constraint_column_id is > 1, then that fk is a compound key. If not, it isn't, it's a 1-1.
Scott,
Maybe I am chasing my tail here but still would like to ask follow-up questions to learn.
Here is the query:
SELECT
OBJECT_NAME(aFKC.Parent_Object_ID) AS Parent_Table
, COL_NAME(aFKC.Parent_Object_ID, aFKC.Parent_Column_ID) AS Parent_Column
--, aC2.NAME AS Parent_Column_Name
, OBJECT_NAME(aFKC.Referenced_Object_ID) AS Child_Table
, COL_NAME(aFKC.Referenced_Object_ID, aFKC.Referenced_Column_ID) AS Child_Column
--, aC1.NAME AS Child_Column_Name
, OBJECT_Name(aFKC.Constraint_Object_ID) AS Foreign_Key_Name
FROM sys.Foreign_Key_Columns aFKC --sys.SysForeignKeys
So basing off of that, we know if a field that is a Primary Key, it would be a "one" relationship and if a field is a Foreign Key of a table, then it would be a "many" relationship. With that reasoning, can we construct the following?
One-to-One (PK to PK)?
One-to-Many (PK to FK)?
Many-to-Many (FK to FK)?
Maybe? I am not sure because for example, one to one could also be a Unique key, rather than a PK.
You can't directly enforce a many-to-many relationship in SQL using foreign keys. The traditional way to do it is to use an intermediate table that has one-to-many relationships in both directions to join the tables. ORMs often bypass that and just implement the relationship without enforcing the constraint (on the assumption that the controlling application will provide those guarantees).
PK to PK would be normal for a one-to-one relationship, but it's always possible that a PK to FK relationship is effectively one-to-one if there is a suitable unique constraint on the table.
Can I find out if if a field of a table is unique or has duplicates by looking in one of the sys tables?
no. for that you will need something like this
select SerialNumber, count(1)
from Hardwares
group by serialnumber
having count(1) > 1The is_unique and is_unique_constraint of sys.indexes can be used (alongside some of the other sys tables) to identify uniqueness guarantees on a table.
AndyC would you say the term to use would be "could be used" 
because uniqueness is explicitly set property 
If is_unique or is_unique_constraint is set for a column (or group of columns) they are guaranteed to be unique, both now and in the future. If you merely select the existing data, then it's possible you may find the data is unique right now but you can't be sure it wouldn't be in future.
Since the OP is trying to distinguish the nature of relationships between tables, knowing something will always remain unique is probably more important than whether it appears to be unique right now. Of course if a unique constraint hasn't been added, you'd have to assume that the data will become non-unique in future but that's probably a safe assumption.
Note that doing this is non-trivial, especially when you start considering compound keys, as you may have to infer uniqueness in multiple ways. For example, if you have a FK comprising of columns A, B, C then constraints like:
unique(a), unique(b,c)
guarantee that (a,b,c) is unique even though it isn't explicitly specified, but
unique(a,d), unique(b,c)
does not.
Note, too, that a PK can be multiple columns, it does not have to be a single column, so it's not safe to assume that it always will be.
As to "many-to-many". I thought you were talking about "many" columns to "many" columns but still a unique resulting value. But if you're talking about multiple rows matching multiple rows, then, no, that's not possible. A FK can consist of many columns, but it must match only a single row in the referenced table.