Technical Article

Effectively mapping primary key – foreign key relations

,

To properly locating the primary key - foreign key relations in a database sounds like a tough task. Mohit Nayyar posted a technique to find out primary key and foreign key that have the same column names. The limitation of that is that, in reality, column names are not always the same in a database. For example, in the AdvantureWorks sample database in SQL2005, the SalesPersonID in Sales.SalesPerson table has a foreign key reference to the EmployeeID column in the HumanResources.Employee table.

This script will map the primary key and foreign key, regardless if the column names are same or not. This script has been tested on both SQL2000 and 2005. This is another evidence of the usefulness of the information_schema view.

If you are interest in a particular column, simple add " and ccu.column_name='<column name>'" in the where cluse.

Mohit's Column: http://www.sqlservercentral.com/scripts/Miscellaneous/31983/

By John Liu, 2007/11/07

select 'PK_Table_Schema'=ccu.table_schema,'PK_Table_Name'=ccu.table_name,'PK_Column_Name'=ccu.column_name,'PK_Constraint_Name'=ccu.constraint_name, 
'FK_Table_Schema'=ccu1.table_schema,'FK_Table_Name'=ccu1.table_name, 'FK_Column_Name'=ccu1.column_name, 
'FK_Constraint_Name'=rc.constraint_name 
from information_schema.constraint_column_usage CCU inner join information_schema.referential_constraints RC
on CCU.constraint_name=RC.unique_constraint_name
inner join information_schema.constraint_column_usage CCU1 on RC.constraint_name=ccu1.constraint_name
where ccu.constraint_name not in (select constraint_name from information_schema.referential_constraints)

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.8 (5)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.8 (5)

You rated this post out of 5. Change rating

Related content

Technical Article

New MDX Book Published

Fast Track to MDX gives readers all the necessary background information needed to write useful, powerful MDX expressions and introduces the most frequently used MDX functions and constructs. No prior knowledge is assumed and examples are used throughout the book to rapidly develop MDX skills to the point where a reader can solve real business problems. A CD containing examples from within the book, and a time-limited version of ProClarity, is included

2003-05-01

2,698 reads

SQLServerCentral Article

How to Build Dynamic Stored Procedures

Robert is our expert on dynamic sql. This week he offers some good hints for planning the contruction of a proc that will use dynamic sql. He also adds some suggestions on how to format the code so that when you return to it later, you can figure out what you were doing!

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.8 (5)

You rated this post out of 5. Change rating

2007-10-02 (first published: )

50,623 reads