主页 / dba / 问题 / 333977
Accepted
ceving
Asked: 2023-12-12 19:03:24 +0800 CST

如何使外键检查 NULL 值？

  • 772

我有一个表指定类型来区分标量和列表。当“标量”存在时，该值是一个标量，当它为空时，它是一个列表。

drop table type1 cascade;
create table type1 (
  id     integer primary key,
  scalar "char",
  name   text,
  unique (id, scalar)
);
insert into type1 values
  (1, 'x', 'single'),
  (2, null, 'multi');

现在我有一个包含值的表，它引用类型并在元组（类型，标量）上添加唯一约束。这是可行的，因为 NULL 是不同的。如果“标量”为 NULL，则每个“类型”可能有多于一行，因为所有“标量”都是不同的。

drop table value1 cascade;
create table value1 (
  id     integer primary key,
  type   integer,
  scalar "char",
  value  text,
  foreign key (type, scalar) references type1 (id, scalar),
  unique (type, scalar)
);

可以插入单个值：

insert into value1 values
  (11, 1, 'x', 'just one');

但不可能再添加一个：

insert into value1 values
  (12, 1, 'x', 'another one'); -- ERROR

但如果标量为 NULL，则可能有多个：

insert into value1 values
  (21, 2, null, 'first'),
  (22, 2, null, 'second');

我的问题：可以引用不存在的类型。

这可行，但不应该：

insert into value1 values
  (12, 1, null, 'another');

允许使用 (1, NULL) 作为类型，尽管类型表中只有 (1, 'x') 和 (2, NULL)。

我尝试在外键上使用match full，但这似乎做了不同的事情。它还阻止了我对多个空值的预期使用，因为它根本拒绝多个空值。

value1.scalar我想在if is NULL中允许多个 NULL type1.scalar。但我想拒绝value1.scalarif type1.scalaris not NULL 中的任何 NULL。我该如何表达这一点呢？

postgresql

1 个回答

  1. Best Answer

    您有两个不同的问题：

    • 的unique约束value1是强制每组 一行(type, scalar)，但您希望非标量允许多行。独特的约束不允许过滤器。
      但是，您可以使用过滤的唯一索引where (scalar is not null)来代替......
    • ...另一个问题是两者MATCH SIMPLE都MATCH FULL没有做你想做的事。你需要MATCH PARTIAL，但这还没有实现。

    相反，使用诸如（NUL 字符）之类的标志值\x0来表示非标量，并使该scalar列在 上不可为空type1。

    create table type1 (
  id     integer primary key,
  scalar "char" not null,
  name   text,
  unique (id, scalar)
);

create table value1 (
  id     integer primary key,
  type   integer,
  scalar "char",
  value  text,
  foreign key (type, scalar) references type1 (id, scalar) match full  -- or match simple (the default) if you prefer
);

create unique index ux on value1 (type, scalar) where (scalar <> '\x0');

    数据库<>小提琴

    • 2

相关问题