SQL 一对多

问题描述

我正在尝试为我们有 channels 的系统构建 SQL 模式，每个通道都有一个 id，以及一个或多个 fixtures.我很难找到实现这种一对多映射的方法.(即一个 channel 到多个 fixtures).我正在使用 H2 数据库引擎.

I am trying to build an SQL schema for a system where we have channels, each with an id, and one or more fixtures. I am having difficulty finding a way to implement this one-to-many mapping. (i.e. One channel to many fixtures). I am using the H2 database engine.

我不能有一张桌子:

 id | fixture
----|----------
  1 |    1
  1 |    2
  2 |    3

CREATE TABLE channel(
  id INT NOT NULL PRIMARY KEY,
  fixture INT NOT NULL
  );

... 因为 PRIMARY KEY id 必须是 UNIQUE.

... as the PRIMARY KEY id must be UNIQUE.

同样，我无法如下映射:

Similarly, I cannot map as follows:

CREATE TABLE channel(
  id INT NOT NULL PRIMARY KEY,
  f_set INT NOT NULL REFERENCES fixtures(f_set)
  );

CREATE TABLE fixtures(
  id INT NOT NULL PRIMARY KEY,
  f_set INT NOT NULL
  );

... 因为这要求 f_set 是 UNIQUE

... as this required f_set to be UNIQUE

我目前的实现方式如下:

I am currently implementing it as follows:

CREATE TABLE channel(
  id INT NOT NULL PRIMARY KEY,
  f_set INT NOT NULL REFERENCES fixture_set(id)
  );

CREATE TABLE fixtures(
  id INT NOT NULL PRIMARY KEY,
  f_set INT NOT NULL REFERENCES fixture_set(id)
  );

CREATE TABLE fixture_set(
  id INT NOT NULL PRIMARY KEY
  );

...但这意味着我们可以有一个 channel 和一个 fixture_set 没有任何分配的 fixtures (不理想).

... but this means that we can have a channel with a fixture_set which does not have any assigned fixtures (Not ideal).

我想知道您是否对我如何处理这个问题有任何建议(或者我的理解有误).谢谢

I was wondering if you had any suggestions for how i may approach this (Or where my understanding is wrong). Thanks

推荐答案

一对多"表示多个项目(可能)引用一个项目.如果它是多个灯具的一个通道，那么灯具应该参考通道，而不是相反，这意味着参考列应该在 fixtures 表中:

"One-to-many" means that many items (may) reference one item. If it's one channel to many fixtures, then fixtures should reference channels, not the other way round, which means the reference column should be in the fixtures table:

CREATE TABLE channel(
  id INT NOT NULL PRIMARY KEY
  );

CREATE TABLE fixtures(
  id INT NOT NULL PRIMARY KEY,
  channel_id INT NOT NULL FOREIGN KEY REFERENCES channel (id)
  );

这篇关于SQL 一对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！