一列的值不能大于另一列
[英] The values of one column cannot be greater than another
本文介绍了一列的值不能大于另一列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试创建一个表,其中一列中的值不能大于下一列。例如,我创建了以下表。
CREATE TABLE价格(
PriceID INT PRIMARY KEY IDENTITY 1),
OriginalPrice FLOAT NOT NULL,
CurrentPrice FLOAT NOT NULL,
折扣FLOAT,
ShippingCost FLOAT NOT NULL,
Tax FLOAT NOT NULL);
当前价格不能大于OriginalPrice。
所以我尝试做的是
CurrentPrice FLOAT CHECK(CurrentPrice< = OriginalPrice)NOT NULL,
但这给我以下错误:
消息8141,级别16,状态0,行1
列CurrentPrice的列CHECK约束引用另一列,即价格表。
Msg 1750,级别16,状态0,行1
无法创建约束。查看以前的错误。我不能在同一个表中引用一个列吗?
CREATE TABLE price(
PriceID INT PRIMARY KEY IDENTITY(1,1),
OriginalPrice FLOAT NOT NULL,
CurrentPrice FLOAT NOT NULL,
折扣FLOAT,
ShippingCost FLOAT NOT NULL,
Tax FLOAT NOT NULL,
CHECK(CurrentPrice< = OriginalPrice));
您也可以在
之后添加,例如
ALTER TABLE价格ADD CHECK(CurrentPrice< = OriginalPrice);
--or
ALTER TABLE价格ADD CONSTRAINT CK_Price_Current_vs_Original
CHECK(CurrentPrice< = OriginalPrice);
I am trying to create a table where the values in one column can't be greater than the next column over. For example, I am creating the following table.
CREATE TABLE Price (
PriceID INT PRIMARY KEY IDENTITY (1,1),
OriginalPrice FLOAT NOT NULL,
CurrentPrice FLOAT NOT NULL,
Discount FLOAT,
ShippingCost FLOAT NOT NULL,
Tax FLOAT NOT NULL);
And Current Price cannot be greater than OriginalPrice.
So what I tried doing was
CurrentPrice FLOAT CHECK (CurrentPrice <= OriginalPrice) NOT NULL,
But this gives me the following error:
Msg 8141, Level 16, State 0, Line 1
Column CHECK constraint for column 'CurrentPrice' references another column, table 'Price'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Am I not allowed to reference a column in the same table?
解决方案
Just change it to a table-level constraint instead of a column constraint.
CREATE TABLE Price (
PriceID INT PRIMARY KEY IDENTITY (1,1),
OriginalPrice FLOAT NOT NULL,
CurrentPrice FLOAT NOT NULL,
Discount FLOAT,
ShippingCost FLOAT NOT NULL,
Tax FLOAT NOT NULL,
CHECK (CurrentPrice <= OriginalPrice));
You can also add it after, e.g.
ALTER TABLE Price ADD CHECK (CurrentPrice <= OriginalPrice);
--or
ALTER TABLE Price ADD CONSTRAINT CK_Price_Current_vs_Original
CHECK (CurrentPrice <= OriginalPrice);
这篇关于一列的值不能大于另一列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!