Pattern based constraint : Check « Constraints « SQL Server / T-SQL Tutorial






5>    CREATE TABLE Customers
6>    (
7>       CustomerNo     int     IDENTITY   NOT NULL
8>          PRIMARY KEY,
9>       CustomerName   varchar(30)        NOT NULL,
10>       Address1       varchar(30)        NOT NULL,
11>       Address2       varchar(30)        NOT NULL,
12>      City            varchar(20)        NOT NULL,
13>      State           char(2)            NOT NULL,
14>      Zip             varchar(10)        NOT NULL,
15>      Contact         varchar(25)        NOT NULL,
16>      Phone           char(15)           NOT NULL,
17>      FedIDNo         varchar(9)         NOT NULL,
18>      DateInSystem    smalldatetime      NOT NULL
19>    )
20>    GO
1>    ALTER TABLE Customers
2>       WITH NOCHECK
3>       ADD CONSTRAINT CN_CustomerPhoneNo
4>       CHECK
5>       (Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
6> go
1>
2>
3>    ALTER TABLE Customers
4>       CHECK
5>       CONSTRAINT CN_CustomerPhoneNo
6>
7> GO
1>
2> drop table Customers;
3> GO
1>
2>
3>








7.5.Check
7.5.1.Using CHECK Constraints
7.5.2.Adding a CHECK Constraint to an Existing Table
7.5.3.Constraints with name
7.5.4.CHECK Clause
7.5.5.Check Constraint
7.5.6.CHECK (DATEPART(DAY, GETDATE()) < 28)
7.5.7.A check constraint uses an expression to qualify records that are acceptable for any Inserts or Updates
7.5.8.Use or to link two conditions for check constraint
7.5.9.Using a Multicolumn CHECK Constraint
7.5.10.Check for data length
7.5.11.Pattern based constraint
7.5.12.Mark nocheck for a constraint
7.5.13.A table-level check constraint that limits Banker IDs to a specific format