Sql 条件非空约束

Question

我很想知道是否可以在 sql 中创建条件非空约束？换句话说，是否可以创建一个约束，使得 B 列可以为空，因为 A 列包含的长可以说是"new"，但是如果 A 列的内容更改为其他内容，则 B 列不再允许为空？
为了扩展这一点，只要 A 列显示"new"，就可以使 B 列必须为空或为空？
谢谢大家 :D

Answer 1

这对于 CONSTRAINT CHECK 来说完全没问题。只需这样做:

要求:

is it possible to create a constraint such that a column B can be null as long column A contains lets say 'NEW' but if the contents of column A changes to something else then column B is no longer allowed to be null?

请注意短语:B 列 可以 为空

解决方案:

create table tbl
(
    A varchar(10) not null,
    B varchar(10),

    constraint uk_tbl check
    (
      A = 'NEW' -- B can be null or not null: no need to add AND here
      OR (A <> 'NEW' AND B IS NOT NULL)
    )
);

您可以进一步简化它:

create table tbl
(
    A varchar(10) not null,
    B varchar(10),

    constraint uk_tbl check
    (
      A = 'NEW' 
      OR B IS NOT NULL
    )
);

与上述要求互不兼容的要求:

And to extend on that, it is then possible to make it so that column B must be null or empty as long as column A says 'NEW'?

请注意短语:B 列 必须为空

create table tbl
(
    A varchar(10) not null,
    B varchar(10),

    constraint uk_tbl check
    (
      (A = 'NEW' AND B IS NULL)
      OR A <> 'NEW'
    )
);

可以用这个简化，更简单但可能不像上面那样可读:

create table tbl
(
    A varchar(10) not null,
    B varchar(10),

    constraint uk_tbl check
    (
      A <> 'NEW'
      OR B IS NULL
    )
);