后记：实现单个收藏夹标志的最佳方式

Question

我有这张桌子:

CREATE TABLE public.data_source__instrument (
  instrument_id int4 NOT NULL,
  data_source_id int4 NOT NULL,
  CONSTRAINT data_source__instrument__pk PRIMARY KEY (data_source_id, instrument_id)
);

为清楚起见，以下是我可能在此表中拥有的数据示例：

我希望能够为每种乐器设置一个最喜欢的数据源。我还希望每种乐器都有且只有一个最喜欢的数据源。

我想到的解决方案如下:

CREATE TABLE public.data_source__instrument (
  instrument_id int4 NOT NULL,
  data_source_id int4 NOT NULL,
  fav_data_source boolean NULL, -- ### new column ###
  CONSTRAINT data_source__instrument__pk PRIMARY KEY (data_source_id, instrument_id),
  CONSTRAINT fav_data_source UNIQUE (instrument_id,fav_data_source) -- ### new constraint ###
);

我选择用值＜code＞true＜code＞标记收藏夹，并将非收藏夹元组设置为＜code＞null＜code＞（因为唯一约束不影响null）。

该解决方案将允许每个仪器在< code>fav_data_source列中最多有一个真值。

示例：

然而，我对这个解决方案并不完全满意。首先，它允许仪器没有最喜欢的数据源。此外，fav_data_source的值可以设置为false，这很混乱，也不是很有用（因为它不能很好地使用唯一约束）。

有更好的方法吗？我忽略了什么吗？

编辑：理想情况下，我更喜欢这个问题的简单解决方案，并避免使用数据库触发器等功能。

Answer 1

我选择用值＜code＞true＜/code＞标记最喜欢的元组，并将非最喜欢的元组设置为＜code＞null＜/code＞（因为唯一约束不影响＜code＞null＜/code＞s），但＜code＞fav_data_source

有两种方法可以改善这种情况:

>

在列上设置检查约束以防止＜code＞false＜/code＞值：

fav_data_source boolean NULL check (fav_data_source IS TRUE or fav_data_source IS NULL)

使用部分索引仅检查具有真实值的行（另请参阅此处）：

fav_data_source boolean NOT NULL
…
CREATE UNIQUE INDEX unique_fav_data_source ON data_source__instrument (instrument_id) WHERE (fav_data_source);

它允许仪器没有最喜欢的数据源。

我认为，在没有触发器的关系数据库中，不可能表达这样一个“x中，至少有一个y必须存在”约束。（假设您仍然希望允许仪器完全没有任何数据源）。

我还希望每种乐器都有且只有一个最喜欢的数据源。

您可以考虑将收藏的数据源存储在仪器表本身中，作为不可为空的外来参考列。

为避免重复，仅将非偏好的(附加/替代)数据源放入关系表，然后提供< code > data _ source _ _ instrument 作为视图:

CREATE VIEW data_source__instrument(instrument_id, data_source_id, is_favourite) AS
SELECT id, fav_data_source_id, true FROM instrument
UNION ALL
SELECT instrument_id, data_source_id, false FROM alternate_data_source__instrument;

此方法的缺点是无法创建对视图的外键引用（如果需要这样做），并且更新数据源变得非常复杂。同样，这只适用于M：N关系，而不是1：N关系，你需要一种方法来断言一个工具最喜欢的源实际上属于该工具（技术上可以使用额外的外键，但真的很丑陋），并且你会引入循环引用，这只是一团糟。

Answer 2

您需要考虑数据库规范化和实体及其关系。具体说来

• 在联合表中使用布尔标志违反了正常形式（对其他行的函数依赖）
• “最喜欢的数据源”是一个独立的实体，您拥有的标志是在描述一个关系时描述一个实体

我假设您有单独的仪器表和数据源表，为了简化您的问题，我们省略了这些表。

这是你需要的：

create table instrument
(
  id serial primary key
);

create table data_source
(
  id serial primary key
);

create table data_source__instrument
(
  data_source_id integer references data_source on delete cascade on update cascade,
  instrument_id integer references instrument on delete cascade on update cascade,
  constraint data_source__instrument_pk primary key (data_source_id, instrument_id)
);

create table favourite_data_source
(
  data_source_id integer not null,
  instrument_id integer not null unique,
  constraint favourite_data_source_pk foreign key (data_source_id, instrument_id) references data_source__instrument (data_source_id, instrument_id) on update cascade on delete cascade
);

现在来复制您的示例：

insert into instrument
values (1),
  (2);

insert into data_source
values (1),
  (2),
  (3);

insert into data_source__instrument (instrument_id, data_source_id)
values (1, 1),
  (1, 2),
  (1, 3),
  (2, 2),
  (2, 3);

insert into favourite_data_source (instrument_id, data_source_id)
values (1, 1),
  (2, 3);

使用此方案

• 每个仪器可以有 0 到 1 个喜欢的数据源
• 您必须单独插入喜欢的数据源数据
• FK将通过级联和检查来处理完整性