CODEWITHSUNDEEP
sqlsql-server
simpati2gb
simpati2gb

Reputation: 17

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

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')), 
)

Upvotes: 1

Views: 460

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

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.

Upvotes: 3

Related Questions