Solved

# Briding tables and forgein keys

Posted on 2011-10-11
274 Views
I am creating a Briding table between two tables with a many-to-many relationship.

The columns which I am including are:

ID from table 1.
ID from table 2.

I am making a primary key across both ID columns in the bridging table.

Do i need to make forgein primary keys?
0
Question by:Mr_Shaw

LVL 39

Assisted Solution

I don't think ther is need to create forgein primary keys

refer this for more details
http://www.singingeels.com/Articles/Understanding_SQL_Many_to_Many_Relationships.aspx
0

Author Comment

I hope I asked the question correctly.

The bridging table joins TableA with TableB.

I have a composite primary key in the bridging table of TableAID and TableBID. I was also thinking of putting a foreign key on each these column to ensure referential integrity.

Is this correct?
0

LVL 39

Assisted Solution

putting a foreign key on each these column to ensure referential integrity is correct.
I thought you are takling about some combined key
0

LVL 142

Assisted Solution

so, you have :
table1 (ID => primary key)
table2 (ID => primary key)
bridge_table
* ID_1 => ID from table1 : hence a foreign key [optional]
* ID_2 => ID from table2 : hence a foreign key [optional]
ID_1 + ID_2 may be a primary key indeed.

0

Author Comment

The PK will be a compound of TableAID and TableBID.

Column TableAID will also be a foreign key.
Column TableBID will also be a foreign key.

It seems straning making a column part of PK as well as FK.

0

LVL 142

Assisted Solution

it is not strange to have columns being PK and FK at the same time.
0

LVL 39

Assisted Solution

See
Primary key is nothing but the unique key for the tables thetfor it must be composite b'coz there might be same value for both the columns

and foreign key is nothing but the primary key of anothet table it will be assigned to each field
0

LVL 3

Assisted Solution

As your Question you have Three Tables and
------------------------------
Table_One
------------------------------
ID1
------------------------------
Table_Two
------------------------------
ID2
------------------------------

Now you Want to Make a table which can link both Table_One and Table_Two

so you need to make third Table like
------------------------------
Table_One_Two
------------------------------
ID (Primary Key)
ID1 (From Table_One Forigen Key)
ID2 (From Table_Two Forigen Key)
------------------------------
0

LVL 39

Accepted Solution

You should DEFINITELY create a clustered composite primary key at the minimum.  This ensures you have unique pairs as well as gives you a clustered key so that your table will not forever grow.  You should consider having a surrogate key (Identity field) if you believe that you will ever need to reference the pair in a different location.  Because as I'm sure you know, a foreign key must reference a primary or unique candidate key.  So if you used a composite key, both fields in the key would need to be in the child table should you need to make a foreign key reference to this new join/intersection table.
0

Author Closing Comment

thanks
0

## Join & Write a Comment Already a member? Login.

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Read about achieving the basic levels of HRIS security in the workplace.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

#### 730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!