mysql - 复杂的SQL关系

Question

我想设计一个包含下表的 MySQL 数据库:

• people，每人一条记录，以及一个 id 索引
• shirts，带有一个 id 索引，每件衬衫属于一个人，由 people_id 字段给出，我们可以在该字段上设置与第一个表的关系
• pants，有一个 id 索引，每件衬衫属于一个人，由 people_id 字段给出，我们可以在该字段上设置与第一个表的关系

然后，在其他一些表中，根据其 people_id、shirt_id 和 pants_id，每条记录都指一个穿着衣服的人。 people_id 并不是绝对必要的，因为我们可以通过查找衣服的主人来找到它。

但是，对于每条记录，shirt_id 和 pants_id 的所有者必须相同，我们不希望任何人借衣服。

我们可以使用复杂的关系语法来强制执行这种关系吗？或者有更好的结构吗？

谢谢!

Answer 1

假设一个人可以拥有多件衣服(不止一件衬衫，不止一条裤子)，那么您所描述的实际上并不是一种复杂的关系，而只是一种一对多的关系。您可以通过使用一个表(人员表)的主键作为其他表中的外键来创建这些表。

CREATE TABLE people (
  id integer primary key auto_increment,
  name varchar(25)
);

CREATE TABLE shirts (
  id integer primary key auto_increment,
  people_id integer,
  foreign key (people_id) references people(id)
);

CREATE TABLE pants (
  id integer primary key auto_increment,
  people_id integer,
  foreign key (people_id) references people(id)
);

采用这种结构，shirts 或 pants 表中的任何行只能属于 people 表中的一行，但任何行people 表中的 shirts 或 pants 表中可能有许多对应的条目。

给定一条 ID 为“5”的裤子，您可以通过以下查询找到其所有者:

SELECT people.*
  FROM people 
    INNER JOIN pants
      ON pants.people_id = people.id
  WHERE pants.id = 5;

同样，如果您想查找 id 为 7 的人的所有衣服，您可以这样做:

SELECT pants.*, shirts.*
  FROM people
    LEFT JOIN pants
      ON pants.people_id = people.id
    LEFT JOIN shirts
      ON shirts.people_id = people.id
  WHERE people.id = 7;

编辑我们在这里使用 LEFT JOIN ，如下所示，因为我们希望它返回值，即使该人没有裤子或衬衫，或两者都没有。

另一方面，如果每个人只能拥有一件衬衫和一条裤子，而您仍然想将它们维护在自己的表中，则可以保留上述表结构并强制执行 UNIQUE 对 pants 和 shirts 表中的 people_id 进行约束。