oracle约束数据类型 [英] oracle constraints datatype

问题描述

我已经创建了一个数据类型为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约束数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！