Cacher is the code snippet organizer for pro developers

-----------------
-- PRIMARY KEY --
-----------------


-----------
-- CHECK --
-----------

-- CHECK can force a column to only accept certain values.

-- how to add CHECK to make it so the amount column can only accept values betwen 10 and 100.
ALTER TABLE orders4
ADD CONSTRAINT CHK_Amt_Range
	CHECK (amount >=10 AND amount <=100);
	
/* Results if a value it inserted that is outside of that check range

Msg 547, Level 16, State 0, Line 79
The INSERT statement conflicted with the CHECK constraint "CHK_Amt_Range". The conflict occurred in database "scratch", table "dbo.orders4", column 'amount'.
The statement has been terminated.

*/

-- how to add CHECK that requires a certain amount of characters that must be within a certain numerical range
ALTER TABLE orders4
ADD CONSTRAINT CHK_ZIP_Format
	CHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]');

-- how to add CHECK that requires a certain amount of characters that must be within a certain alpha range
ALTER TABLE orders4
ADD CONSTRAINT CHK_Promo_Code
	CHECK (PromoCode LIKE '[A-E][F-G][C-N][A-O]');
	
-- how to add CHECK without checking the previously existing data for conformity to the CHECK, using the NOCHECK clause. If you have invalid data in the column already and you don't use NOCHECK, it will not let you create the CHECK in the first place.
ALTER TABLE orders4 WITH NOCHECK
ADD CONSTRAINT CHK_ZIP_Format
	CHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]');