这个查找(关联)表是否正确?(许多许多关系)

CREATE TABLE jokecategory (
  jokeid INT NOT NULL,
  categoryid INT NOT NULL,
  PRIMARY KEY (jokeid, categoryid)
) DEFAULT CHARACTER SET utf8;

especially PRIMARY KEY (jokeid, categoryid)? or is there a better way to write this?

尤其是主键(jokeid, categoryid)?或者有更好的方法来写这个?

Thank you in advance;-)

谢谢你提前;-)

3 个解决方案

#1

Yes it is a perfectly good table and primary key (I might call this an "association" table, but I would not call it a "lookup" table).

是的，它是一个非常好的表和主键(我可能把它称为“关联”表，但我不会把它称为“查找”表)。

Some people (not I) would insist on having a surrogate key column jokecategoryid as the primary key; if you do that you still need a UNIQUE constraint on (jokeid, categoryid) to enforce the business rule.

有些人(不是我)坚持使用代理键列jokecategoryid作为主键;如果这样做，您仍然需要对(jokeid, categoryid)有一个惟一的约束来执行业务规则。

#2

I would expect the table to have two foreign keys e.g.

我希望这张桌子有两把外国钥匙。

CREATE TABLE jokecategory (
  jokeid INT NOT NULL REFERENCES joke (jokeid), 
  categoryid INT NOT NULL REFERENCES category (categoryid), 
  PRIMARY KEY (jokeid, categoryid)
) DEFAULT CHARACTER SET utf8;

#3

Yes, it is a perfectly good table, as Tony has already pointed out. But I see no need to create it as a regular heap table with a primary key index. That's two storage structures. Just the index structure would do, so my advice is to create this table as an index organized table.

是的，正如托尼已经指出的那样，这是一张非常好的桌子。但是我认为没有必要将它创建为具有主键索引的常规堆表。这是两个存储结构。只要使用索引结构就可以了，所以我的建议是将这个表创建为一个索引组织的表。

Here is a link to the documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/indexiot.htm#CNCPT911

这里有一个指向文档的链接:http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/indexiot.htm#CNCPT911

Regards, Rob.

问候,抢劫。

#1