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
);
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
);
Alternatively, you could write:
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