how to query phone number must be between 5 and 15 characters

Question

How to add constraint to StaffPhone - it must be between 5 and 15 characters:

CREATE TABLE MsStaff
(
    StaffID CHAR(5) NOT NULL PRIMARY KEY,
    StaffName VARCHAR(50) NOT NULL,
    StaffGender VARCHAR(6) NOT NULL,
    StaffEmail VARCHAR(50) NOT NULL,
    StaffPhone VARCHAR(15) NOT NULL,
    StaffAddress VARCHAR(100) NOT NULL,
    StaffSalary VARCHAR(100) NOT NULL,

    CONSTRAINT StaffIDRule CHECK(StaffID LIKE 'ST[0-9][0-9][0-9]'),
    CONSTRAINT StaffPhoneRule CHECK (LEN(StaffPhone) >= 5),
    CONSTRAINT StaffGenderRule CHECK (StaffGender IN ('Male','Female')), 
)

Possible duplicate... https://stackoverflow.com/questions/36045875/constraint-for-phone-number-in-sql-server — SS_DBA, Jun 04 '19 at 12:10
Possible duplicate of [Constraint for phone number in SQL Server](https://stackoverflow.com/questions/36045875/constraint-for-phone-number-in-sql-server) — EzLo, Jun 04 '19 at 12:17
@iainc that is exactly the wrong way to go. You make data constraints in the database, not the application. — Sean Lange, Jun 04 '19 at 12:36
@iainc why you told like this.? any specific reason for that. because, here we have the lot of db constraints. — Pugal, Jun 04 '19 at 12:39
Considering that the column is a `varchar(15)` you only need to check if the value has a length greater than or equal to 5; the data type will already constrain the value to 15 characters. — Thom A, Jun 04 '19 at 12:42
i tend to think of a column as a box to keep stuff. Instead of constraining the box, you constrain the entrance to the box. Feels easier and more flexible. Happy to be wrong though :-) — iainc, Jun 04 '19 at 16:28

score 3 · Accepted Answer · answered Jun 04 '19 at 12:10

With a check constraint:

alter table MsStaff add constraint chk_msstaff_staffphone
    check (len(staffphone) between 5 and 15);

However, your existing constraint should do this. The length of the character is 15 and you are checking that there are at least 5 characters.

how to query phone number must be between 5 and 15 characters

1 Answers1