0

I want to know how can I determine relationships between data which is not determined by the primary key in the design.

For example, I have three tables in the database like this :-

table 1: fields 
table 2: area 
table 3: location

All of the tables have data but the person who created the database did not explain the primary keys and the foreign keys in it. How can I know the relationship between these tables?

2
  • 2
    That's not possible without inside knowledge of your data. Only by seeing the data you can grasp what's it describing. Maybe see if there's an app making use of it and check if any of its methods have better descriptions of the entities.
    – Marian
    Dec 23, 2014 at 20:43
  • 2
    As @Marian said - there is no way to know without doubt what is related to what without a) being the designer or seeing the design documents or b) reverse-engineering the code that writes to the tables in-toto.
    – Hannah Vernon
    Dec 23, 2014 at 22:35

2 Answers 2

1

When I have encountered similar problems to this in the past, I have used the query that marc_s provides here: Know relationships between all the tables of database in SQL Server

It should give you a good place to start?

Here it is replicated in case the other thread is removed in the future (credit - marc_s):

SELECT
fk.name 'FK Name',
tp.name 'Parent table',
cp.name, cp.column_id,
tr.name 'Refrenced table',
cr.name, cr.column_id
FROM 
sys.foreign_keys fk
INNER JOIN 
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id =      cp.object_id
INNER JOIN 
sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
tp.name, cp.column_id
1
  • 1
    this is script its useful just on the database which have relationships but in my case who created the database doesn't select the primary keys in all the tables , and he want to me to try to understand how can we make a relationship between them . Dec 23, 2014 at 13:12
0

Usually in sql server tables have primary key, that key is the relator of the table to be used in queries even if foreign keys are not set, if you open the tables one by one in design view you'll see the primary field marked with key symbol, if you don't see that, open the table in data view and inspect the data integrity try to find the main column in table that holds data with unique nature. Also when you open the table in design view check if the developer have any description for the table or the fields. Otherwise I need to see those tables to help you further.

Not the answer you're looking for? Browse other questions tagged or ask your own question.