SQL 在ORACLE中创建表时如何限制INTEGER的长度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8837846/
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 restrict the length of INTEGER when creating a table in ORACLE?
提问by MengT
all
全部
When creating a table in Oracle sql*plus, I want to restrict that the length of an INTEGER column can only be 8.
在 Oracle sql*plus 中创建表时，我想限制一个 INTEGER 列的长度只能是 8。
eg: the RegNumber is an INTEGER, and it must be a 8 digit number.
例如：RegNumber 是一个整数，它必须是一个 8 位数字。
How can I do this when I creating a table?
创建表时如何执行此操作？
回答by N West
The INTEGER datatype is just a subtype of NUMBER. You can define the column as NUMBER(8,0) to get you an integer column that is <= 8 digits.
INTEGER 数据类型只是 NUMBER 的一个子类型。您可以将该列定义为 NUMBER(8,0) 以获得 <= 8 位的整数列。
If you are trying to ensure that the column is 8 digits and ONLY 8 digits, you'll need to add a check constraint on the column:
如果您试图确保该列是 8 位数字且只有 8 位数字，则需要在该列上添加检查约束：
CREATE TABLE RegTable
(RegNumber NUMBER(8,0),
CONSTRAINT CheckRegNumber CHECK (RegNumber > 9999999)
);
回答by APC
Just specify a length of 8 and a precision of 0. Like this
只需指定长度为 8 和精度为 0。像这样
SQL> create table t8 (col1 number(8,0))
2 /
Table created.
SQL> insert into t8 values (12345678)
2 /
1 row created.
SQL> insert into t8 values (123456789)
2 /
insert into t8 values (123456789)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SQL>
To enforce an exact length (all numbers must be eight digits long) you'll need to use a CHECK constraint:
要强制执行确切的长度（所有数字必须为八位数字），您需要使用 CHECK 约束：
SQL> alter table t8
2 add constraint t8_ck check (length(col1) = 8)
3 /
Table altered.
SQL> insert into t8 values (1234567)
2 /
insert into t8 values (1234567)
*
ERROR at line 1:
ORA-02290: check constraint (APC.T8_CK) violated
SQL>
