blocks|key|1719175|text|我将使用第二个连接表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1719176|MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID|code-block|syntax|javascript|1719177|主键将是两列的组合。您还可以从Movie和Category表的每一列获得一个外键。|offset|length|style|CODE|1719178|连接表将如下所示：|1719179|create+table+movie_category_junction
(
++movie_id+int,
++category_id+int,
++CONSTRAINT+movie_cat_pk+PRIMARY+KEY+(movie_id,+category_id),
++CONSTRAINT+FK_movie+
++++++FOREIGN+KEY+(movie_id)+REFERENCES+movie+(movie_id),
++CONSTRAINT+FK_category+
++++++FOREIGN+KEY+(category_id)+REFERENCES+category+(category_id)
);|1719180|参见SQL+Fiddle+with+Demo。|1719181|使用这两个字段作为PRIMARY+KEY将防止将重复的电影/类别组合添加到表中。|1719182|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlfiddle.com/#!3/002f1/2^0|0|0|F|5|L|8|0|0|0|2|K|0|0|9|B|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@$I|15|J|16|K|L]|$I|17|J|18|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1A|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@$I|1C|J|1D|1|1E]]|A|$]]|$1|S|3|T|5|6|7|1F|8|@$I|1G|J|1H|K|L]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

I would use the second junction table:

<pre><code>MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID
</code></pre>

The primary key would be the combination of both columns. You would also have a foreign key from each column to the <code>Movie</code> and <code>Category</code> table.

The junction table would look similar to this:

<pre><code>create table movie_category_junction
(
 movie_id int,
 category_id int,
 CONSTRAINT movie_cat_pk PRIMARY KEY (movie_id, category_id),
 CONSTRAINT FK_movie 
 FOREIGN KEY (movie_id) REFERENCES movie (movie_id),
 CONSTRAINT FK_category 
 FOREIGN KEY (category_id) REFERENCES category (category_id)
);
</code></pre>

See <a href="http://www.sqlfiddle.com/#!3/002f1/2">SQL Fiddle with Demo</a>.

Using these two fields as the <code>PRIMARY KEY</code> will prevent duplicate movie/category combinations from being added to the table.

blocks|key|1715080|text|关于这一点，有不同的思想流派。一所学校更喜欢包括一个主键，并命名链接表，而不仅仅是它正在链接的两个表。原因是，尽管表一开始看起来只是一个链接表，但它可能会成为包含重要数据的自己的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1715081|杂志和订阅者之间的多对多就是一个例子。实际上，该链接是一个具有其自身属性的订阅，如过期日期、支付状态等。|1715082|然而，我认为有时一个链接表就是一个链接表。与类别的多对多关系就是一个很好的例子。|1715083|因此，在这种情况下，不需要单独的一个字段主键。你可以有一个自动分配的键，它不会伤害任何东西，并将使删除特定记录变得更容易。这在一般实践中可能是很好的，所以如果该表后来发展成一个具有自己的重要数据(作为订阅)的重要表，它将已经有了一个自动分配的主键。|1715084|您可以将唯一索引放在这两个字段上，以避免重复。如果你有一个单独的自动分配键，这甚至可以防止重复。您可以将这两个字段用作主键(这也是唯一索引)。|1715085|因此，一个学派可以坚持整数自动分配主键，并避免复合主键。这不是唯一的方法，也可能不是最好的方法，但它不会把你引向错误，陷入你真正后悔的问题。|1715086|但是，对于您正在做的事情，您可能只需要这两个字段就可以了。我仍然建议让这两个字段成为复合主键，或者至少在这两个字段上放置一个唯一索引。|1715087|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

There are different schools of thought on this. One school prefers including a primary key and naming the linking table something more significant than just the two tables it is linking. The reasoning is that although the table may start out seeming like just a linking table, it may become its own table with significant data.

An example is a many-to-many between magazines and subscribers. Really that link is a subscription with its own attributes, like expiration date, payment status, etc.

However, I think sometimes a linking table is just a linking table. The many to many relationship with categories is a good example of this.

So in this case, a separate one field primary key is not necessary. You could have a auto-assign key, which wouldn't hurt anything, and would make deleting specific records easier. It might be good as a general practice, so if the table later develops into a significant table with its own significant data (as subscriptions) it will already have an auto-assign primary key.

You can put a unique index on the two fields to avoid duplicates. This will even prevent duplicates if you have a separate auto-assign key. You could use both fields as your primary key (which is also a unique index).

So, the one school of thought can stick with integer auto-assign primary keys, and avoids compound primary keys. This is not the only way to do it, and maybe not the best, but it won't lead you wrong, into a problem where you really regret it.

But, for something like what you are doing, you will probably be fine with just the two fields. I'd still recommend either making the two fields a compound primary key, or at least putting a unique index on the two fields.

blocks|key|6020373|text|我会选择第二个连接表。但将这两个字段作为主键。这将限制重复条目。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6020374|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I would go with the 2nd junction table. But make those two fields as Primary key. That will restrict duplicate entries.

I'm using Microsoft SQL Server Management Studio and while creating a junction table should I create an ID column for the junction table, if so should I also make it the primary key and identity column? Or just keep 2 columns for the tables I'm joining in the many-to-many relation?

For example if this would be the many-to many tables:

<pre><code>MOVIE
Movie_ID
Name
etc...

CATEGORY
Category_ID
Name
etc...
</code></pre>

Should I make the junction table: 

<pre><code>MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID
Movie_Category_Junction_ID
</code></pre>

[and make the <code>Movie_Category_Junction_ID</code> my Primary Key and use it as the Identity Column] ?

Or:

<pre><code>MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID
</code></pre>

[and just leave it at that with no primary key or identity table] ?

MS SQL creating many-to-many relation with a junction table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在使用Microsoft SQL Server Management Studio，在创建连接表时，我是否应该为该连接表创建一个ID列，如果是，我是否也应该将其设置为主键和标识列？或者只为多对多关系中要连接的表保留两列？例如，如果这是多对多的表：MOVIEMovie_IDNameetc...CATEGORYCate...

问MS SQL使用连接表创建多对多关系
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MS SQL使用连接表创建多对多关系EN