oracle 如何在 SQL 中的两列之间创建检查约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/585020/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create a check constraint between two columns in SQL?
提问by Unknown
I am trying to create a Basic pay (BP) table with
我正在尝试创建一个基本工资 (BP) 表
CREATE TABLE bp (
bpid VARCHAR(5),
FOREIGN KEY (bpid) REFERENCES designation(desigid),
upperlimit DECIMAL(10,2) NOT NULL,
lowerlimit DECIMAL(10,2) NOT NULL,
increment DECIMAL(10,2) NOT NULL
CONSTRAINT llvalid CHECK (upperlimit > lowerlimit)
);
As you can see near the ending, I want to check if upperlimitis greater than lowerlimit, how can I do that?
正如你在结尾处看到的，我想检查是否upperlimit大于lowerlimit，我该怎么做？
回答by Jens Schauder
It might (probably does) depend on the data base you use.
它可能（可能确实）取决于您使用的数据库。
Comparing to the oracle syntax (e.g. here: http://www.techonthenet.com/oracle/check.php), what you are missing might be a ',' between NULL and CONSTRAINT
与 oracle 语法（例如：http: //www.techonthenet.com/oracle/check.php）相比，您缺少的可能是 NULL 和 CONSTRAINT 之间的“,”
回答by Unknown
Here's proper the SQL query...
这是正确的 SQL 查询...
CREATE TABLE bp (bpid VARCHAR(5),
FOREIGN KEY (bpid) REFERENCES designation(desigid),
upperlimit DECIMAL(10,2) NOT NULL,
lowerlimit DECIMAL(10,2) NOT NULL,
increment DECIMAL(10,2) NOT NULL,
CONSTRAINT llvalid CHECK (upperlimit > lowerlimit));
Note the comma after NOT NULL and CONSTRAINT in the last line.
注意最后一行中 NOT NULL 和 CONSTRAINT 后面的逗号。
回答by MikeW
The problem is that you have defined it as a column level constraint but it references other columns. You must define a constraint at the table level.
问题是您已将其定义为列级约束，但它引用了其他列。您必须在表级别定义约束。
ALTER TABLE bp
ADD CONSTRAINT CK_limit CHECK ( upperlimit > lowerlimit)
