SQL条件非空约束 [英] Sql Conditional Not Null Constraint

问题描述

我很好奇，是否有可能在sql中创建条件非null约束?换句话说，是否可以创建一个约束，使得只要A列包含"NEW"，那么B列就可以为空，但是如果A列的内容更改为其他内容，那么B列就不再允许为空?
并对此进行扩展，那么有可能使之保持不变，只要A列显示"NEW"，B列就必须为空或为空?
谢谢大家:D

I am curious to know is it possible to create a conditional not null constraint in sql? In otherwords 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?
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'?
Thanks All :D

推荐答案

对于CONSTRAINT CHECK，这是完全可以的.只需这样做:

This is perfectly fine for CONSTRAINT CHECK. Just do this:

要求:

是否可以创建约束，以使列B可以为空 只要A列包含让我们说"NEW"，但是如果A列的内容 如果更改了其他内容，则不再允许B列 空吗?

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列可以为空

Note the phrase: column B can be null

解决方案:

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
    )
);

---

要求与以上要求互不相容:

---

Requirement mutually incompatible to requirement above:

并对此进行扩展，然后可以使它成为B列 只要A列显示"NEW"，该字段必须为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列必须为空

Note the phrase: column B must be null

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

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

可以通过此方法进行简化，简化，但可能不如上面那样可读:

Could be simplified with this, simpler but might not be as readable as above though:

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

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

这篇关于SQL条件非空约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！