How to create a relationship?

How do I create a relationship between two tables when the two columns that should connect each table have different names?

i.e. Table Person Column Name < -- > Table Employee Column Person Name

The two columns actually contain the same data type but different field name and size. I know this involves a series of steps. What are they? Could you please include some sample code.

A question for you, are you going to be enforcing this relationship via a constraint?

Scenario 1 - You are going to be enforcing this relationship.

SQL Server requires that both columns in a referential constraint have the same data type and length (size). They are not required to have the same name. If you are going to do this my recommendation is to use a SQL Server Management Studio 2005 to create the relationship while using diagram view, it will be easiser for a person who is just starting with SQL Server.

Scenario 2 - You are not going to be enforcing this relationship, you just need to query data ...

In this case you could just write a fancy T-SQL Select statement to get the data from the Database regardles of DataType, Length (size), or Field Names . The SELECT statement you would have to write would use a JOIN statement to specify that you are going to be querying the database for the information on two tables, and you would specify the fields (condition) to join the information by on the ON statement.

Ex.

SELECT *
FROM table1
JOIN table2
ON table1.field1 = table2.field2

Hope this helps,

Roberto Hernández-Pou
http://www.rhpconsulting.net

|||

open the database diagram

add the tables that you need

drag the field from the parent table to the child table.

this will create a relationship

requirements. the field from the parent table

should either have unique index or is a primary key

|||

Hi,

The basic requirements for establishing relationship(s) among tables is to set a logical DB design and then identify the objects:

Tables and their names (ENTITIES)