英文:
Create a constraint based on column value to ensure not null
问题
我有一个包含4列的表，列名分别是：col_1, col_2, col_3, col_4，其中 col_1 的数据类型是 TEXT，而其他列是 INTEGER。
我想要在 col_1 上添加以下约束：
如果 (col_1 = 'a')，那么 col_2 不能为 NULL
如果 (col_1 = 'b')，那么 col_2 和 col_3 都不能为 NULL
如果 (col_1 = 'c')，那么 col_4 不能为 NULL
我尝试使用 CHECK 约束，如下所示：
ALTER TABLE "my_table"
ADD CONSTRAINT check_for_type
CHECK ((col_1 = 'a' AND col_2 IS NOT NULL)
OR (col_1 = 'b' AND (col_2 IS NOT NULL AND col_3 IS NOT NULL))
OR (col_1 = 'c' AND col_4 IS NOT NULL)
);
但是仍然允许我添加一个具有 col_1 = 'a' 和 col_2 = NULL 的行。
对于任何帮助，我将不胜感激。
提前感谢。
英文:
I have a table with 4 columns, let's say: col_1, col_2, col_3, col_4, where col_1 is of type TEXT and the others are INTEGER.
I want to add a constraint on col_1 that goes as follow:
if (col_1 = 'a') then col_2 cannot be NULL
if (col_1 = 'b') then col_2 AND col_3 cannot be NULL
if (col_1 = 'c') then col_4 cannot be NULL
I tried to use a CHECK as follows:
ALTER TABLE "my_table"
ADD CONSTRAINT check_for_type
CHECK ((col_1 = 'a' AND 'col_2' IS NOT NULL)
OR (col_1 = 'b' AND ('col_2' IS NOT NULL AND 'col_3' IS NOT NULL))
OR (col_1 = 'c' AND 'col_4' IS NOT NULL)
);
But it is still allowing me to add a row with col_1 = 'a' and col_2 = NULL
I'd be grateful for any help.
Thanks in advance
答案1
得分: 0
'col_2' IS NOT NULL
将始终为真，因为它正在将文本'col_2'的硬编码字符串与null进行比较。它没有将该列中的值与null进行比较。
要引用列中的值，您将需要使用双引号或不使用引号。
您可能正在寻找：
ALTER TABLE "my_table" ADD CONSTRAINT check_for_type
CHECK (
(col_1 = 'a' AND col_2 IS NOT NULL)
OR (col_1 = 'b' AND (col_2 IS NOT NULL AND col_3 IS NOT NULL))
OR (col_1 = 'c' AND col_4 IS NOT NULL)
);
这只是将'col_2'/'col_3'/'col_4'视为列名而不是硬编码字符串的原始约束。
英文:
'col_2' IS NOT NULL
will always be true since it is comparing a hardcoded string of the text 'col_2' against null. It is not comparing the value in that column with null.
To refer to the value in the column, you will want to use double-quotes or no quotes.
What you are looking for is likely:
ALTER TABLE "my_table" ADD CONSTRAINT check_for_type
CHECK (
(col_1 = 'a' AND col_2 IS NOT NULL)
OR (col_1 = 'b' AND (col_2 IS NOT NULL AND col_3 IS NOT NULL))
OR (col_1 = 'c' AND col_4 IS NOT NULL)
);
This is just your original constraint treating col_2/col_3/col_4 as column names instead of hardcoded strings.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论