您的位置: 首页  >  技术问答  >  多个表之间的SQL多对多关系

多个表之间的SQL多对多关系

分类: 技术问答 • 2022-04-01 20:17:54

多个表之间的SQL多对多关系

问题描述：

我有一个要在SQL上创建的数据库，并且试图将这些关系连接在一起.有三个表:superhero，power和superheroPower.表超级英雄和力量是由表superheroPower表示的多对多关系.

I have a database I'm trying to create on SQL and I am trying to connect the relationships together. There are three tables: superhero, power, and superheroPower. The tables superhero and power is a many to many relationship which is represented by the table superheroPower.

以下语法对于表(以及其他所有表)之间的外键是否正确?另外，关于这些表格的设置，还有其他建议吗?

Is the syntax below correct for foreign keys between tables (and everything else)? Also, is there any other recommendations on these tables in terms of their setup?

CREATE TABLE superhero( id INT NOT NULL AUTO_INCREMENT, 
heroName VARCHAR(255) NOT NULL, 
firstName VARCHAR(255), 
lastName VARCHAR(255), 
firstAppearance DATE, 
gender VARCHAR(255), 
bio TEXT, 
universe VARCHAR(255), 
PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE power( 
id INT NOT NULL AUTO_INCREMENT, 
name VARCHAR(255) NOT NULL, 
description TEXT NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE superheroPower( 
superheroID INT, 
powerID INT, 
PRIMARY KEY(superheroID, powerID), 
FOREIGN KEY(superheroID) REFERENCES superhero(id), 
FOREIGN KEY(powerID) REFERENCES power(id) 
) ENGINE=InnoDB;

答

是的，那里的一切看起来都还不错.但是...

Yes, everything there looks okay. But...

一些注意事项:

我们将对gender列使用较短的数据类型；我看不到我们需要255个字符来表示. (强制执行的行的最大大小是有限制的.)如果只有很少的值，我们将考虑使用ENUM数据类型.

We'd use a shorter datatype for the gender column; I don't see that we'd need 255 characters to express that. (There is a limit on the maximum size of a row which is enforced.) If there only a few values for that, we'd consider ENUM datatype.

我们还可能在其中的几列上添加NOT NULL约束，例如英雄名称，名字，姓氏.我们还可能添加DEFAULT ''.有时候，出于某些原因，我们确实确实确实需要允许使用NULL值，但是我们会尽可能使用NOT NULL.

We'd also likely add NOT NULL constraints on several of those columns, such as heroname, firstname, lastname. We'd also likely add DEFAULT ''. Sometimes, we really do need to allow NULL values for some reason, but we use NOT NULL wherever we can.

我对TEXT列犹豫不决.使用TEXT数据类型没有错，但是我只是怀疑那些数据类型可能隐藏"了一些最好存储在其他列中的信息.

I'm hesitant about the TEXT columns. There's nothing wrong with using TEXT datatype, but I'm just suspicious that those may be "hiding" some information that might better be stored in additional columns.

对于外键，我们将按照使用的模式为约束分配一个名称，并可能添加ON UPDATE CASCADE ON DELETE CASCADE

For the foreign keys, we'd assign a name to the constraints, following the pattern we use, and also likely add ON UPDATE CASCADE ON DELETE CASCADE

CONSTRAINT FK_superheroPower_power FOREIGN KEY (powerID) 
  REFERENCES power(id) ON UPDATE CASCADE ON DELETE CASCADE

关于标识符(表名和列名)的注释

A note about identifiers (table names and column names)

我们这样做的方式是，所有表名都是小写. (我们有一个MySQL选项集，它强制所有表名都使用小写.)我们这样做是为了避免不同操作系统/文件系统的不兼容问题(其中一些区分大小写，有些则不区分大小写).

The way we do it, all table name are lower case. (We have a MySQL option set that forces all table names to lower case.) We do this to avoid incompatibility issues for different operating systems/filesystems (some of which are case sensitive, and some are not).

此外，表名称为单数.该表的名称表示该表的一行.我们也不会在名称中包含_table.

Also, table names are singular. The name of the table names what one row of the table represents. We also don't include _table as part of the name.

MySQL中的列名从不区分大小写，但我们也总是对列名使用小写.我们不会"camelCase"列名，而是使用下划线字符作为分隔符，例如power_id与powerID，hero_name与heroName.

Column names in MySQL are never case sensitive, but we always use lower case for the column names as well. We don't "camelCase" our column names, we use underscore character as separators, e.g. power_id vs. powerID, hero_name vs. heroName.

关注

我上面的注释"不是必须遵循的特定规则；这些只是我们使用的模式.

My "notes" above aren't specific rules that must be followed; those are just patterns we use.

遵循这些模式并不能保证我们将拥有成功的软件，但确实可以帮助我们.

Following these patterns does not guarantee that we'll have a successful software, but it does help us.

为便于参考，我将展示这些表格在我们商店中的初切"外观，以举例说明另一种模式；这不是正确的方法"，而是我们作为团队所确定的一种方法".

For your reference, I'll show how these tables would look as a "first cut" from our shop, as an illustration of another pattern; this is not "the right way", it's just "a way" that we've settled on as a team. 

CREATE TABLE superhero
( id               INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'pk'
, hero_name        VARCHAR(255) NOT NULL                COMMENT ''
, first_name       VARCHAR(255) NOT NULL DEFAULT ''     COMMENT ''
, last_name        VARCHAR(255) NOT NULL DEFAULT ''     COMMENT ''
, first_appearance DATE                                 COMMENT 'date superhero first appeared'
, gender           ENUM('female','male','other')        COMMENT 'female,male or other'
, biography_text   TEXT                                 COMMENT ''
, universe         VARCHAR(255)                         COMMENT ''
, PRIMARY KEY(id)
, UNIQUE KEY superhero_UX1 (hero_name) 
) ENGINE=InnoDB;

CREATE TABLE power
( id               INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'pk'
, name             VARCHAR(255) NOT NULL                COMMENT ''  
, description_text TEXT NOT NULL                        COMMENT '' 
, PRIMARY KEY(id)
, UNIQUE KEY power_UX1 (name)
) ENGINE=InnoDB;

CREATE TABLE superheropower
( superhero_id   INT UNSIGNED NOT NULL         COMMENT 'pk, fk ref superhero'
, power_id       INT UNSIGNED NOT NULL         COMMENT 'pk, fk ref power'
, PRIMARY KEY(superhero_id, power_id)
, CONSTRAINT FK_superheropower_superhero 
     FOREIGN KEY(superhero_id) REFERENCES superhero(id)
     ON UPDATE CASCADE ON DELETE CASCADE
, CONSTRAINT FK_superheropower_power
     FOREIGN KEY (power_id) REFERENCES power(id) 
     ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

相关推荐