The CHECK constraint rejects any entry that contains an alpha character
Add CHECK programmatically
You can add a CHECK constraint programmatically when you create the table or after by executing a Transact-SQL (T-SQL) ALTER TABLE statement. The following T-SQL adds a new column to the example table:
USE AdventureWorks
ALTER TABLE dbo.PostalCodes
ADD PostalCodeExtended char(9) NULL
GO
ALTER TABLE dbo.PostalCodes
ADD CONSTRAINT CKPostalCodesPostalCodeExtended
CHECK (PostalCodeExtended LIKE
\'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]\')
GO
The first statement adds a character column named PostalCodeExtended. The next statement uses the ADD CONSTRAINT clause to add a CHECK constraint that forces number characters. The only difference is that this column expects a nine-character ZIP code value. Similarly to the manual example, the constraint will reject any value that contains an alpha character.
The image below shows what happens when you try to enter a value that's fewer than nine characters or that contains an alpha character -- the table accepts only one value, 406045555.