在检查约束中使用case语句 [英] Using a case statement in a check constraint
问题描述
上周我一直在学习SQL,但是我不确定如何在检查约束中正确添加case语句.有人可以给我指点吗?
i'v been learning SQL for the last week but I am unsure how to correctly add a case statement within a check constraint. Can anybody give me any pointers?
我有下面的成绩表:
CREATE TABLE Grade
(
salary_grade char(1) NOT NULL CHECK (salary_grade = UPPER(salary_grade)),
CONSTRAINT ck_grade_scale CHECK(
CASE
WHEN salary_grade = '[A-D]'
THEN salary_scale = 'S1'
WHEN salary_grade = '[D-G]'
THEN salary_scale = 'S2'
END)
salary_scale char(2) DEFAULT 'S1' NOT NULL,
CONSTRAINT pk_grade PRIMARY KEY (salary_grade),
CONSTRAINT ck_salary_grade CHECK (REGEXP_LIKE(salary_grade, '[A-G]', 'c')),
--constraint must be either S1 or S2
CONSTRAINT ck_salary_scale CHECK (salary_scale IN ('S1', 'S2'))
);
我想检查salary_grade
是否在A-D之间,则salary_scale
必须为'S1',或者salary_grade
是否在E-G之间,则为'S2'.
I want to check that if the salary_grade
is between A-D then the salary_scale
must be 'S1' or if the salary_grade
is between E-G then it's 'S2'.
我试图对此进行研究并提出后者,但是它不起作用..我是否正确构造了代码?
I have tried to research this and come up with the latter but however it does not work.. have I structured the code correctly?
推荐答案
我认为您可以执行以下操作:
I think you can do the following:
CREATE TABLE Grade
(
salary_grade char(1) NOT NULL CHECK (REGEXP_LIKE(salary_grade, '[A-G]', 'c')),
salary_scale char(2) DEFAULT 'S1' NOT NULL,
CONSTRAINT pk_grade PRIMARY KEY (salary_grade),
CONSTRAINT ck_grade_scale CHECK ( REGEXP_LIKE(salary_grade, '[A-D]', 'c') AND salary_scale = 'S1'
OR REGEXP_LIKE(salary_grade, '[E-G]', 'c') AND salary_scale = 'S2' )
);
您不需要salary_grade
上的UPPER()
约束,因为正则表达式检查就足够了(您已经在检查以确保它是A和G之间的大写字母).我认为单独对salary_scale
的约束也没有必要,因为从逻辑上讲,它将包含在最后一个约束中.
You don't need the UPPER()
constraint on salary_grade
since the regex check will suffice (you're already checking to make sure it's an uppercase letter between A and G). I don't think the constraint on salary_scale
alone is necessary either since it would be contained, logically, in the last constraint.
更新
以下是使用CASE
语句的方法:
Here is how you might do it with a CASE
statement:
CREATE TABLE Grade
(
salary_grade char(1) NOT NULL CHECK (REGEXP_LIKE(salary_grade, '[A-G]', 'c')),
salary_scale char(2) DEFAULT 'S1' NOT NULL,
CONSTRAINT pk_grade PRIMARY KEY (salary_grade),
CONSTRAINT ck_grade_scale CHECK ( salary_scale = CASE WHEN REGEXP_LIKE(salary_grade, '[A-D]', 'c') THEN 'S1' ELSE 'S2' END )
);
这篇关于在检查约束中使用case语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!