Creating a many-to-many relationship

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating a many-to-many relationship

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2011-04-05 : 19:19:52

I want to create a many-to-many relationship between Stores and Products

Stores
--------
StoreID (PK)
Name

Products
--------
ProductID (PK)
Name

StoreProducts
--------
StoreID (FK)
ProductID (FK)

In StoreProducts, should StoreID and ProductID be primary keys? If not, do I need to add a primary key field?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-05 : 19:28:52

They should become a composite primary key. Some people will say to just create another identity column in StoreProducts, but I am not in that camp as that identity column would never get used. I believe those two should be the PK there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Apples
Posting Yak Master

146 Posts

Posted - 2011-04-05 : 21:46:02

quote:
Originally posted by tkizer

They should become a composite primary key. Some people will say to just create another identity column in StoreProducts, but I am not in that camp as that identity column would never get used. I believe those two should be the PK there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Great, thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-05 : 22:48:56

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources