SQL Server中电话号码的约束 [英] Constraint for phone number in SQL Server

问题描述

约束电话号码为7位数字.如何检查SQL Server中的7位数字?

Constraint for phone number to be of 7 digits. How to check if it is of 7 digits in SQL Server?

CREATE TABLE Customer
(
    C_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    C_Name VARCHAR(255) NOT NULL,
    Phone INT 
);

推荐答案

请勿将电话号码存储为整数.例如，某些有效数字可能以0开头-如果不是今天(也许不是将来).要进行验证检查，可以使用like:

Do not store phone numbers as integers. Some valid numbers, for instance, could start with a 0 -- if not today, perhaps in the future. To do the validation check, you can use like:

CREATE TABLE Customer (
    C_ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    C_Name VARCHAR(255) NOT NULL,
    Phone CHAR(7), -- you might not want to have such a precise length
    CONSTRAINT chk_phone CHECK (phone not like '%[^0-9]%') -- check that no number is not a digit 
);

或者，您可以写:

CONSTRAINT chk_phone CHECK (phone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') -- check that no number is not a digit 

这篇关于SQL Server中电话号码的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！