SQL 在SQL Server中创建表时如何限制INTEGER的长度？

How to restrict the length of INTEGER when creating a table in SQL Server?

提问by Midhun Mathew

When creating a table in SQL SERVER, I want to restrict that the length of an INTEGER column can only be equal 10.

在 SQL SERVER 中创建表时，我想限制一个 INTEGER 列的长度只能等于 10。

eg: the PhoneNumber is an INTEGER, and it must be a 10 digit number.

例如：PhoneNumber 是一个整数，它必须是一个 10 位数字。

How can I do this when I creating a table?

创建表时如何执行此操作？

回答by a_horse_with_no_name

If you want to limit the range of an integer column you can use a check constraint:

如果要限制整数列的范围，可以使用检查约束：

create table some_table
(
   phone_number integer not null check (phone_number between 0 and 9999999999)
);

But as R.T. and huMpty duMpty have pointed out: a phone number is usually better stored in a varcharcolumn.

但正如 RT 和 huMpty duMpty 指出的那样：电话号码通常最好存储在varchar列中。

回答by anonxen

If I understand correctly, you want to make sure the entries are exactly 10 digits in length.

如果我理解正确，您要确保条目的长度正好是 10 位数字。

If you insist on an Integer Data Type, I would recommend Bigint because of the range limitation of Int(-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647))

如果您坚持使用整数数据类型，我会推荐 Bigint，因为 Int(-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)) 的范围限制

CREATE TABLE dbo.Table_Name(
    Phone_Number BIGINT CONSTRAINT TenDigits CHECK (Phone_Number BETWEEN 1000000000 and 9999999999)
    );

Another option would be to have a Varchar Field of length 10, then you should check only numbers are being entered and the length is not less than 10.

另一种选择是使用长度为 10 的 Varchar 字段，然后您应该检查仅输入数字并且长度不小于 10。

回答by sangram

As per me Phone number should not be stored in integer as we are not going to do any numeric operation on it like adding dividing .we are going to treat it as string for e.g. finding all number with ISD say '91' or STD '022' etc secondly if you switch to make it integer you have to handle overflow

按照我的说法，电话号码不应以整数形式存储，因为我们不会对其进行任何数字运算，例如添加除法。我们将其视为字符串，例如查找所有带有 ISD 的数字，例如“91”或 STD“022” ' 等其次，如果你切换到整数，你必须处理溢出

回答by Rahul Tripathi

I would recommend you to use varcharas phone number(only for phone numbers as some phone numbers may contain hyphen,phus sign) and restrict the length to 10ie, varchar(10).

我建议您使用varchar作为电话号码（仅用于电话号码，因为某些电话号码可能包含连字符、phus 符号）并将长度限制为10ie, varchar(10)。

As correctly pointed by a_horse_with_no_name in comments you can put constraint on the numbers to be of specified range like this:

正如 a_horse_with_no_name 在注释中正确指出的那样，您可以像这样对指定范围内的数字进行约束：

check (phone_number between 0 and 9999999999)

Also on a side note:-

另外附注：-

You will receive a error message like this if you use numbers outside the range of int -2147483648through 2147483647

如果您使用 int 范围之外的数字 - 2147483648到2147483647，您将收到这样的错误消息

Arithmetic overflow error converting expression to data type int.

将表达式转换为数据类型 int 时出现算术溢出错误。

So you will not be able to use all the int of length 10 in your case.

因此，在您的情况下，您将无法使用所有长度为 10 的 int。

回答by huMpty duMpty

I don't think there is a way to limit if you use number fields like int, bigint, smallint, and tinyint

如果您使用int、bigint、smallint 和 tinyint等数字字段，我认为没有办法限制

Make a varchar(10)field and validate before insert

varchar(10)在插入之前创建一个字段并验证

Still you need to use int field to store the phone number, you will need to restrict before in your application

您仍然需要使用 int 字段来存储电话号码，您需要在应用程序之前进行限制

回答by Navneet

Make column varchar and create a check that it strictly should have 10 characters

制作列 varchar 并创建一个检查，它严格应该有 10 个字符

create table some_table
(
   phone_number varchar(10) check (len(phone_number)=10)
);

回答by ibozkurt79

My recommendation is:

我的建议是：

CREATE TABLE trial_table (phone_number VARCHAR(13));

The column can be used for international numbers too.

该列也可用于国际号码。

回答by Thorsten Kettner

First consider internal and external format:

首先考虑内部和外部格式：

Yes, a telephone number can be stored as an integer. You would have to assure however that all numbers are stored in the same format, e.g. as the international number without the plus sign. 4940123456 would then be a German number for instance, as 49 is the German country code. To analize the number later, however, would be difficult; country codes can be 1 to 4 digits, followed by a presumably unknown number of area code digits. But just to know a number and not to know its structure may be sufficient for your purposes. With check constraints you could assure that the number is positive and not longer than, well, how long is the longest number allowed? Be aware: Everytime you show the number, you may have to format the output (in the example given: add a leading plus sign to the number).

是的，电话号码可以存储为整数。但是，您必须确保所有号码都以相同的格式存储，例如作为没有加号的国际号码。例如，4940123456 将是德国号码，因为 49 是德国国家代码。然而，稍后分析这个数字会很困难。国家/地区代码可以是 1 到 4 位数字，后跟可能未知数量的地区代码数字。但是仅仅知道一个数字而不知道它的结构可能就足以满足您的目的。通过检查约束，您可以确保该数字为正数且不超过允许的最长数字多长？请注意：每次显示数字时，您可能都必须格式化输出（在给出的示例中：向数字添加前导加号）。

The other way would be to store phone numbers as strings. That would make it possible to store numbers such as '[phone]'. Then the internal format is the same as the external. Advantage: You wouldn't have to think of formatting the output everytime you show the number. But you could even change the format on output if you wanted (remove dashes or replace the plus sign with the actual county dial code or remove country and area code for local calls, etc.) You would have to decide whether to enforce a certain format or not. If not, then numbers could look very different '123456', '004940123456', '040/123456', ... To enforce a certain format, you would write a function (because of the complexity of such a format) and use that in a check constraint. Or write an insert trigger (this should be a BEFORE INSERT trigger, because you want to change a value; as T-SQL doesn't provide this, you would use an INSTEAD OF INSERT trigger instead) to have the field formatted as you desire.

另一种方法是将电话号码存储为字符串。这将使存储诸如“[phone]”之类的数字成为可能。那么内部格式与外部相同。优点：每次显示数字时，您都不必考虑格式化输出。但是您甚至可以根据需要更改输出格式（删除破折号或用实际县拨号代码替换加号或删除本地电话的国家和地区代码等）您必须决定是否强制执行某种格式或不。如果不是，那么数字可能看起来非常不同 '123456', '004940123456', '040/123456', ... 要强制执行某种格式，您将编写一个函数（因为这种格式的复杂性）并使用它在检查约束中。