在 Oracle SQL 中检查约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16400965/
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
CHECK CONSTRAINT in Oracle SQL
提问by Almanzt
I have the following table Goods_In_Wagon(Goods_ID,Wagon_ID,Total_Weight). I need to create a number of if statement check constraint that says
我有下表 Goods_In_Wagon(Goods_ID,Wagon_ID,Total_Weight)。我需要创建一些 if 语句检查约束，表示
"IF WAGON_ID is between 90 and 99 THEN Total_Weight must be greater than 10." AND "IF WAGON_ID is between 100 and 110 THEN Total_Weight must be greater than 20." AND "IF WAGON_ID is between 111 and 120 THEN Total_Weight must be greater than 30."
“如果 WAGON_ID 介于 90 和 99 之间，那么 Total_Weight 必须大于 10。” AND “如果 WAGON_ID 介于 100 和 110 之间，那么 Total_Weight 必须大于 20。” 并且“如果 WAGON_ID 介于 111 和 120 之间，那么 Total_Weight 必须大于 30。”
is this possible in SQL Oracle, if it is how can I implement it
这在 SQL Oracle 中是否可能，如果是我该如何实现它
回答by Ed Gibbs
Use an out-of-line constraint:
使用外部约束：
CREATE TABLE Goods_In_Wagon (
Goods_ID NUMBER(whatever),
Wagon_ID NUMBER(whatever),
Total_Weight NUMBER(whatever),
CONSTRAINT Check_WagID_Weight
CHECK (Wagon_ID NOT BETWEEN 90 AND 99 OR Total_Weight > 10)
)
If the Wagon_IDis notbetween 90 and 99, the constraint passes. If it is between 90 and 99, the Total_Weightmust be greater than 10.
如果Wagon_ID是没有90和99之间，约束传递。如果在 90 到 99 之间，则Total_Weight必须大于 10。
An out-of-line constraint like this allows you to apply the constraint logic at the row level, meaning it can use any of the column values.
像这样的外部约束允许您在行级别应用约束逻辑，这意味着它可以使用任何列值。
AddendumHere's how to handle the updated question with ranges of Wagon_IDand Total_Weight. There are probably other ways but this felt like the "cleanest", meaning it was easiest for me personally to read :)
附录以下是如何处理范围为Wagon_ID和的更新问题Total_Weight。可能还有其他方法，但这感觉像是“最干净的”，这意味着我个人最容易阅读:)
CREATE TABLE Goods_In_Wagon(
Goods_ID NUMBER(whatever),
Wagon_ID NUMBER(whatever),
Total_Weight NUMBER(whatever),
CONSTRAINT Check_WagID_Weight
CHECK (
(Wagon_ID < 90) OR
(Wagon_ID BETWEEN 90 AND 99 AND Total_Weight > 10) OR
(Wagon_ID BETWEEN 100 AND 110 AND Total_Weight > 20) OR
(Wagon_ID BETWEEN 111 AND 120 AND Total_Weight > 30) OR
(Wagon_ID > 120)
)
)
回答by Declan_K
Revised to reflect the additional requirements
修订以反映额外要求
CREATE TABLE Goods_In_Wagon
(
Goods_ID
,Wagon_ID
,Total_Weight
CONSTRAINT check_Weight
CHECK (
WAGON_ID < 90
OR
(TOTAL_WEIGHT > 10 AND WAGON_ID >= 90 AND WAGON_ID <= 99)
OR
(TOTAL_WEIGHT > 20 AND WAGON_ID >= 100 AND WAGON_ID <= 110)
OR
(TOTAL_WEIGHT > 30 AND WAGON_ID >= 111 AND WAGON_ID <= 120)
OR
WAGON_ID > 120
)
)
