• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Briding tables and forgein keys

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
Mr_Shaw
Asked:
Mr_Shaw
  • 3
  • 3
  • 2
  • +2
7 Solutions
 
Pratima PharandeCommented:
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
 
Mr_ShawAuthor Commented:
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
 
Pratima PharandeCommented:
putting a foreign key on each these column to ensure referential integrity is correct.
I thought you are takling about some combined key
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Mr_ShawAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it is not strange to have columns being PK and FK at the same time.
0
 
Pratima PharandeCommented:
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
 
amittripathi8Commented:
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
 
BrandonGalderisiCommented:
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
 
Mr_ShawAuthor Commented:
thanks
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now