Solved Determine Join Type Between Tables In MSSQL Diagram View? (3 Viewers)

[dalski]

Silly question - how does one determine the existing join-type between a table-relationship from the Diagram-View in MSSQL 2022? Also can't see any indicators in the Table-Design itself.
There seems to be a bug in double-clicking the relationship line does not open the relationship (articles suggest this should work but that's the least of my worries atm); so I use the below. But I cannot see how table relationships are defined; one-one, one-many...?

Insert & Update Spec seems to be referential integrity so that's fine, just don't understand how to determine what type of relationship exists/ modify?

I see you can get all sorts, but surely there is a simplistic relationship type between PK's & FK's somewhere.

Most search results are about queries; not table relationships. My first impression seems it may be related to Constraints, but doesn't seem too clear atm. I understand when viewing a table's contents SQL runs to process the table in MSSQL, but the table's relationship schema must be in here somewhere .

 

[tvanstiphout]

SQL Server has system views you can query to get all kinds of information about the structure of the database, including:
select * from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id

You may want to additionally join this with sys.columns to pick up the column names.

 

[dalski]

Thanks Tom that is helpful, but wow what a headache. On such an intrinsic, core area of normalization there must be a simple quick way built into MSSQL .

So we can see the relationships between keys in the Object Browser & it's Properties Window when clicking the relationship in Diagram View, and also by clicking the ellipsis in Properties > Tables & Columns Spec; stumped the relationship type is not in there; let alone in Properties Or Object Browser. But surely somewhere possible to see the actual relationship type. Dare I say it, Access is superior in it's simplicity here. Don't get me wrong even my feeble mind sees the benefit of MSSQL, but if it is not possible to see relationship types in a simplistic manner (especially in the design view) then am I the only one who sees this as a huge disappointment?

 

[BlueSpruce]

You can see the join type by generating an Entity Relationship diagram and clicking on the join symbol.

 

[GPGeorge]

Not to be argumentative, but that is a very simple query in Post #2. And it provides an overabundance of information.

You can also reference the information in the ER diagram as in Post #3.

That said, there's still another way to get a list of Foreign Keys, using TSQL. I got this version from my assistant, Claude. I asked for specific columns. You can further customize it to suit your needs and preferences.

SELECT 
    fk.name AS ForeignKeyName,
    'FOREIGN KEY' AS ConstraintType,
    OBJECT_NAME(fk.parent_object_id) + ' references ' + OBJECT_NAME(fk.referenced_object_id) AS ConstraintDescription,
    fkc.parent_column_id AS ParentColumnID,
    pc.name AS ParentColumnName,
    SCHEMA_NAME(pt.schema_id) + '.' + pt.name AS ParentTable,
    fkc.referenced_column_id AS ReferencedColumnID,
    rc.name AS ReferencedColumnName,
    SCHEMA_NAME(rt.schema_id) + '.' + rt.name AS ReferencedTable,
    CASE fk.delete_referential_action
        WHEN 0 THEN 'NO ACTION'
        WHEN 1 THEN 'CASCADE'
        WHEN 2 THEN 'SET NULL'
        WHEN 3 THEN 'SET DEFAULT'
    END AS DeleteAction,
    CASE fk.update_referential_action
        WHEN 0 THEN 'NO ACTION'
        WHEN 1 THEN 'CASCADE'
        WHEN 2 THEN 'SET NULL'
        WHEN 3 THEN 'SET DEFAULT'
    END AS UpdateAction
FROM 
    sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns pc ON fkc.parent_column_id = pc.column_id 
        AND fkc.parent_object_id = pc.object_id
    INNER JOIN sys.columns rc ON fkc.referenced_column_id = rc.column_id 
        AND fkc.referenced_object_id = rc.object_id
    INNER JOIN sys.tables pt ON fk.parent_object_id = pt.object_id
    INNER JOIN sys.tables rt ON fk.referenced_object_id = rt.object_id
ORDER BY 
    ParentTable, 
    ForeignKeyName, 
    fkc.constraint_column_id;

Save that as a view or as a Stored Proc in your database. It could be parameterized in a Stored Proc to look at a single table, for example,

While it does require more than a few clicks in the interface, it is something that can be easily set up once, after which it is part of your permanent tool kit.

 

[dalski]

You can see the join type by generating an Entity Relationship diagram and clicking on the join symbol.

Thanks @BlueSpruce, yes I've done this. The properties & objects pic'd were from the relationship diagram on a single click selection. As stated double-click does not work.

There seems to be a bug in double-clicking the relationship line does not open the relationship (articles suggest this should work but that's the least of my worries atm); so I use the below. But I cannot see how table relationships are defined; one-one, one-many...?

Where does one actually see the join type; as stated I can't see it in the pic's posted. Are the endpoints different symbols as per most ERD software, Visio/ Access' Relationship view? Regardless there should be a dedicated pane for this; given the many, many tools in MSSQL.

You can also reference the information in the ER diagram as in Post #3.

Thanks @GPGeorge, no offence taken, yes picked a simple one as trying to get my bearings in MSSQL. I must be being thick, where in Nr-3 does it state the actual join type one-many/ one-one... ? Yes PK & FK shown, but where is join-type?
I will create the 'toolkit' but to reiterate points in Nr-3.

Thanks again.

 

[BlueSpruce]

Where does one actually see the join type; as stated I can't see it in the pic's posted

The infinity symbol on the join connector means it's a one to many relationship.

 

[dalski]

Thank you @BlueSpruce, oh so endpoints are different like other software. I'm still really surprised there isn't more functionality in separate panes on this topic being normalization is such an intrinsic topic in db's.

 

[BlueSpruce]

Thank you @BlueSpruce, oh so endpoints are different like other software. I'm still really surprised there isn't more functionality in separate panes on this topic being normalization is such an intrinsic topic in db's.

If you RightClick on the connection line and select edit, you can see the current join type and change it if needed.

 

[dalski]

I'm only able to view Properties; which don't seem to have the join-type, which lead me to pic's posted in Nr-1.

 

[GPGeorge]

Thank you @BlueSpruce, oh so endpoints are different like other software. I'm still really surprised there isn't more functionality in separate panes on this topic being normalization is such an intrinsic topic in db's.

Let's step back and consider who the primary users are for Access and for SQL Server.

Also, let's consider which pool of users is more likely to want a lot of GUI interface assistance regarding tables and table design, and which pool of users is more likely to want to work "closer to the metal" with tables and table design and the system objects in SQL Server.

To me, it's more likely that the basic tools available in SSMS are more tailored to the kind of work engaged in by SQL Server DBAs than the tools available in Access. And the reverse is probably true.

The tools are there in SSMS; they're just aimed at a different, more in-depth working environment where knowledge of TSQL and the ability to see and work directly with system objects is more the rule.

 

[dalski]

Yes George, I appreciate that, but given the thousands of GUI's/ panes which do have direct selection for every property/ attribute/ object known to man in a single click through a GUI; yet absent on one of the most fundamental concepts of a db I find shocking. I do appreciate my lack of experience is likely a factor, but that's my first impression, which brightens your day knowing my amateur perception now .

 

[BlueSpruce]

... let's consider which pool of users is more likely to want a lot of GUI interface assistance regarding tables and table design, and which pool of users is more likely to want to work "closer to the metal" with tables and table design and the system objects in SQL Server. ... The tools are there in SSMS; they're just aimed at a different, more in-depth working environment where knowledge of TSQL and the ability to see and work directly with system objects is more the rule.

So Access has friendlier tools than SQL Server because Access users don't want to work "closer to the metal"? I would think the main purpose for having friendlier GUI tools is to save time instead of having to write long queries to get what you're looking for.

 

[dalski]

I would think the main purpose for having friendlier GUI tools is to save time instead of having to write long queries to get what you're looking for.

I could not agree more. I think it'd be a relevant counter-argument if there was not many GUI's, but there are thousands for such trivial things, yet to repeat myself yet again, probably the most important thing in a db (normalization) is absent.

 

[BlueSpruce]

the most important thing in a db (normalization) is absent.

It's not absent, it's just not enforced because there are certain situations where denormalized tables are needed.

https://www.netdata.cloud/academy/normalized-vs-denormalized/

 

[dalski]

Absent in GUI in Nr-3...

 

[GPGeorge]

So Access has friendlier tools than SQL Server because Access users don't want to work "closer to the metal"? I would think the main purpose for having friendlier GUI tools is to save time instead of having to write long queries to get what you're looking for.

Good point, but I think you took the wrong emphasis. I'm saying that it's more likely a SQL Server DBA would want to work closer to the basic objects, and would, no doubt, be better prepared to do so and would be less likely to consider SSMS inadequate.

This discussion is primarily about one of many tools available to work with SQL Server--SQL Server Management Studio. Because it's a free download generally installed when you install a local instance of SQL Server, it's the one most people know.

However, there are other tools, some free, some paid, which provide many more of the kinds of features and tools people seem to want. Redgate is one of the more widely known. Also, a free tool called dBeaver is available. I'm sure a search will turn up others. You're not limited to SSMS if you find it lacking and don't want to invest time in exploring objects yourself.

One way or the other, anyone who really needs to dig down into the architecture should have multiple ways to do that.

 

[GPGeorge]

I could not agree more. I think it'd be a relevant counter-argument if there was not many GUI's, but there are thousands for such trivial things, yet to repeat myself yet again, probably the most important thing in a db (normalization) is absent.

Please elaborate. "...probably the most important thing in a db (normalization) is absent." Are you saying normalization is absent in SQL Server? Or absent in SSMS?

Neither is true, of course. But maybe that's not what you meant to imply.

 

[dalski]

Please elaborate. "...probably the most important thing in a db (normalization) is absent." Are you saying normalization is absent in SQL Server? Or absent in SSMS?

No that's not the protagonist, @tvanstiphout & @BlueSpruce got it from OP. I've regurgitated the topic many times now. Thanks for trying though , last thing I want to do is be rude. Your effort was appreciated.

 

[Albert D. Kallal]

Actually, the answer is a round about way is kind of funny!

SSMS designer lets you setup relationships, but it actually NEVER calls them anything!

(so, no names like “one to many” or “one to one” etc. is ever used, nor displayed).

On the other hand?

I don’t consider that a many to many relationships exists. You have involved 3 tables for this to occur!

And even for a one to one? Again, that's not a defined relationship in a database.

And such a relation only becomes one to one if you set a unique index on the FK. And often if not, most diagram tools don’t tell you this in the relationship window.

With the above choice (of relating a child table) then you can build any database on the planet.

I also NEVER use the term junction table with my dev team.

If you have a customer, and say you want to list out or enter their favorite kinds of foods?
(allow many kinds of favorite foods)
Well, to some, that means a junction table.
To me? No, it means I have a table called:

tblListOfFavoriteFoods.

It is a child table of the table customers – there is NO such defined term in relational database theory called "many to many”, or even "one to one"

Such things exist beyond that of having setting up a relationship.

So, many to many ONLY exists if you introduce a 3rd table, and relationships are NEVER setup between 3 tables – only between two tables!

Now of course to implement the above, we probably would use a sub form, and each row would have a combo box to select the favorite food. So, we would have the FK column (pointing up to the customer record, and then another column to hold the FavoriteFood_ID (that probably points to our table of favorite foods to choose from).


So, our mistake in Access land?
We assumed that a relationship setting exists to define that many to many. It does NOT. It is ONLY you the developer that decided to involve 3 tables to achieve this goal!

So, SSMS does not really show this information, because it’s a design choice, and one that is NOT enforced with a relationship.

Now, I suppose that the ER diagram “should” go and fetch MORE then the fact of a defined relationship exists. But, those additional meanings (such as one to one)? They are NOT actually part of the relationship, but are achieved by using additional settings in the database (such as setting a unique index on the FK column).

So, should this system figure out, and “tell you”?

Sure, perhaps, but then again, people would make the case for many such “telling the developer” all kinds of information!

The issue really centers on that we are “merging” the concept of a related table with that of concepts like “many to many” and now we stuck in the mindset that the relationship was setup that way. (NO, it was NOT!!!). It was the additional settings outside of the relationship settings that gave rise to this being a one-to-one relationship.

So, should additional concepts like what the tax rate is, or things like some unique index be displayed and you told as such in the ER diagram? Hum, difficult call.

It’s our failure to separate out the concept of a related table vs that of additional rules and constraints that we add AFTER creating that relationship.

If we are to get all “hoity-toity” and go all computer science theory here?

Codd’s rules for database theory NEVER does define a term “one to one” as a formal term!
One to one relationship arises from database design choices, not relational choices!

So, “one to one”, based on computer science writings?
It is NOT a feature nor property of the relational model, but such relationships arise from the developer choosing to enforce additional constraints.

So, at the computer science and theory level in our industry?

A one-to-one relationship is NOT part of the relational model!

You MUST choose additional design constraints beyond that, and things that are NOT PART of the relational setup that gives rise to the one-to-one outcome.

You in fact can’t define a one to one using ONLY relational database theory – you have to toss in additional things (constraints).

So, the above explains much why SSMS does not tell you more then that defined relationship, since database theory says that a relationship has ZERO to do with the relationship becoming a one to one – that’s a design choice achieved through constraints, not though the relationship, nor concepts of setting up a defined relationship.

And it also a developer design choice to say set some tax rates in the database. These are things that arise from developer design choices – not that we setup a relationship between two tables.

I mean, I really never worried much that SSMS does not hold my hand, and tell me the kind of relationship that setup, but is something that is not defined in the relationship anyway!

So, I always considered the type of relation being setup as a database design and constraint point of view, and I always kept the concept of the relationship between two tables a separate concept. Turns out SSMS also thinks this way….

I mean, for that customer example?
We have this in Access:

And we have this in SSMS:

Really, not much of an issue here!

So, the short answer?
A one-to-one relationship is not part of the relational database design spec.

So, we are confusing conceptual issues vs that of implementation and database design issues.

Access does not make these distinctions, and thus brings together multiple concepts,
but SSMS does not merge the concept of relations and that of constraints.

However, while in the SSMS ER diagram? You can right click on a table, and show the index(s).
And if you named the index with a "nice" name, like many to one?

Then you get/have this:

So, you can name the index or constraint with something that means something to you the developer....

And often? Sometimes it just the happy place you spent a lot of time with! - (our lovable Friend called Access).

And, sometimes, comming from other far away lands? We miss things. So, for example, the SSMS diagram tool (a weak tool) does allow one to show the column names - kind of nice..... eg this:

R
Albert