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屋!