问题描述
我已经创建了一个数据类型为smallint的表。
I have created a table with datatype smallint.
create table test(
A smallint,
constraints ACHECK check(A between 1 and 5));
我想添加约束,该约束仅允许用户添加1〜5之间的整数值范围。
但是,即使有约束,我仍然能够插入自动舍入的浮点值。
I want to add constraint that only allow users to add integer value range between 1~5.But, even with the constraints, I am still able to insert floating point value which gets round up automatically.
insert into test values(3.2);
如何使此代码显示错误?
我不允许更改数据类型。
How do I make this code to show an error?I am not allow to change datatype.
推荐答案
这个评论太长了。
您无法轻松完成自己想做的事情。 Oracle将输入值 3.2
转换为整数。整数满足约束条件。值 3
是插入的值。转换发生在幕后。 Oracle的开发人员认为此转换是一件好事。
You cannot do what you want easily. Oracle is converting the input value 3.2
to an integer. The integer meets the constraint. The value 3
is what gets inserted. The conversion happens behind the scenes. The developers of Oracle figured this conversion is a "good thing".
您可以通过将列声明为 number 并对其进行检查来解决此问题。是一个整数:
You could get around this by declaring the column as a number and then checking that it is an integer:
create table test (
A number,
constraints ACHECK check(A between 1 and 5 and mod(A, 1) = 0)
);
这篇关于oracle约束数据类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!