Database with thousand of tables and no foreign keys.

Question

Database with thousand of tables and no foreign keys.

Gamleur84

Default port

Points: 1475
More actions
June 5, 2019 at 3:13 am

#3650294
Hi,
I have a database with thousands of tables. I wanted to see the relationship between tables and the database diagram showed zero table relations. I ran the query from List tables with most relationships in SQL Server database and found that the database has zero foreign keys. Actually it has zero for all this:
- relationships - number of relationships (FKs and FK references)
- foreign_keys - number of foreign keys in a table
- references - number of FK references from other tables
- related_tables - number of different related tables (regardless of relationship type/direction table is counted only once)
- referenced_tables - number of different tables referenced with FKs (please note that table can be related more than once so
- number of FKs and number of referenced tables can be different)
- referencing_tables - number of different tables referencing with foreign keys this table
Am I missing something ? Is there any other way tables could be linked together that this query will not tell me ? I have tables like SALESORDER and SALESLINE, they should be linked somehow, no ?
Thank you
June 7, 2019 at 9:51 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

If there are no Foreign keys then all of the bullet points you listed will be true.

was the database created by some form of "code first" method where the application generates the tables? or maybe some entity framework?

if so you are going to have to work it out by trawling through the c# code

if not you might try something like using syscolums … something like "select name ,count(id) from syscolumns group by name"

then follow that up with "select * from syscolumns where name='.....' "

that might give you a few matches, but only if whoever created the database used a consistent naming pattern (customer one one table, customer on another)

sorry, can't think of another way

MVDBA

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

At that point you'll have to look to the application that uses the database and figure out how they're related.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

You are in a world of pain. There is literally no easy way out. At all. So very sorry.

If, and it's probably a huge "IF", you are lucky and they have consistent naming, table is named MyTable and the primary key is MyTableID or MyTableGUID or MyTableKey or something else consistent, and then, every where else in the database where that column is used, it's also named the same way, a huge set of caveats and I haven't even started, you might be able to query the system tables as @mvdba suggested.

Let's assume that. Let's assume that whatever they did, they were consistent in the naming and with a few queries, you can readily identify primary keys (there are primary keys right?) and then find all the places where a given primary key is used. Assumption done. You still won't be able to implement foreign key constraints. First, it's probably a 100% certainty that you have orphaned records all over creation. Either the associated value was deleted, or, because there are no constraints, the wrong value was used to insert data. You'll be spending days, weeks, cleaning this data. However, you still won't be able to implement foreign keys because, second, your code is, again, almost 100% certainty, assuming they don't exist. You'll put a foreign key in place and then the code, which commonly writes partial or incorrect data into the database, will break. You'll be dropping all the foreign keys again. This is because, I assure you, the one thing you can NEVER do to fix bad design, is actually change the bad design. Oh yeah, they want the database to be better, faster, more accurate. So, go do that. What's that? The problem is the code? Nah, it works fine. It's the database. Can't you put an index on it? SQL Server sucks. We're going to NoSQL.

Best of luck.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

Grant is 100% right

also front end developers in some cases build their child objects first, meaning they put "employee address" records into the database before they put the employee record into the database.

that means, that if you try and put foreign keys in place it may break your application.

Maybe the best you can do is get a diagramming tool and if you find a relationship, then add it to the diagram

ps - Grant, your session in London at sql in the city was awsome.. but you should shave 🙂

MVDBA

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Are there any functions, views or stored procedures in the DB?

If these were written by someone who knows the schema, you may be able to glean some ideas about the implied relationships by examining the JOIN conditions.

Can you ask the DB developers for an ER diagram? [Clutches at straws...]

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Gamleur84 Default port Points: 1475 More actions · Answer 6

Hi

Thank you for all the responses.

It is correct to say the database was created by some form of "code first" method where the application generates the tables. Actually, the schema is managed by code, there is a "synchronize" button in the application that allows me to create the schema objects based on the code definition.

The naming of the tables and fields are indeed consistent, so I can guess the links between the tables, but impossible to generate the database diagram by using SSMS.

I will talk with developers to understand how the integrity of the data is maintained through the code without foreign keys.

thank you for all the responses.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 7

Gamleur84 wrote:

I will talk with developers to understand how the integrity of the data is maintained through the code without foreign keys.

We know the answer to that, which also means the integrity is not guaranteed especially when it comes to someone making direct changes (which are inevitable and virtually guaranteed to occur).

I wouldn't touch it with a 10 foot pole until someone "gets the religion" of DRI. Just make sure that you have a "Restore Plan"... especially one to recover individual tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Gamleur84 wrote:

I will talk with developers to understand how the integrity of the data is maintained through the code without foreign keys. thank you for all the responses.

It won't be. At least it won't be consistently unless you've got the unicorn development team (and since any really good dev team WOULD use foreign keys since they enhance not just data integrity, but performance, yes, they make things run faster, not slower, chances you're working with a set of unicorns is zero).

I'm so sorry. I hate being the bearer of bad news.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 9

Gamleur84 wrote:

Hi Thank you for all the responses. It is correct to say the database was created by some form of "code first" method where the application generates the tables. Actually, the schema is managed by code, there is a "synchronize" button in the application that allows me to create the schema objects based on the code definition. The naming of the tables and fields are indeed consistent, so I can guess the links between the tables, but impossible to generate the database diagram by using SSMS. I will talk with developers to understand how the integrity of the data is maintained through the code without foreign keys. thank you for all the responses.

Well is this something that was created by your company internally or something that is a major product? A major application will almost certainly have some development tool that will show you the relationships and possibly give you an ERD from there. Unless your developers are straight up writing pure SQL from scratch for everything there's meta data somewhere that defines the structures and how they're related.

Gamleur84 Default port Points: 1475 More actions · Answer 10

Hi

Yes, it is a major product from Microsoft, it is their ERP system Dynamics AX. There is no way that the product (database) has data integrity issues as suggested in this post, but I understand that DBAs are commenting on what they know and I need to fill the gap.

Considering the fact that I don't see any foreign keys in the database, I was looking for other way to ensure data integrity between tables. It sounds like there is nothing else to look at from a database perspective, but maybe in the code. That is one thing I need to check.

Also, I am second guessing myself now, did I miss something ? Did the query I run is correct ?

This reply was modified 5 years, 5 months ago by Gamleur84.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 11

As it is Dynamics AX some of the foreign keys would be created and maintained by the users through the application - in many cases this is not done for lack of understanding of the users on how to correctly configure the product.

see https://docs.microsoft.com/en-us/dynamicsax-2012/developer/how-to-add-a-relation-to-a-table

you may also wish to look at find all relations on dynamics ax table

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 12

next you'll be telling us Santa Clause and unicorns don't exist

MVDBA

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 13

Oh, god. It's really Dynamics. People were cracking jokes about this on Twitter.

Yeah, there is literally no easy way around this. Microsoft purchased this horrible piece of software and then released it to the wild, promoting it, and it's a nightmare. There is some documentation, but it's not complete. This is one of those points of pain you're going to have to just live with.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning