----------------------------------------------------------------
--                                                              
--  File:  Create_Check_Constraints_Fixed.SQL                   
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
----------------------------------------------------------------
USE tpce
GO

-- *** CHARGE ***
IF NOT EXISTS ( SELECT name FROM sys.check_constraints Where name = 'CK_CH_CHRG' )
    ALTER TABLE CHARGE ADD CONSTRAINT CK_CH_CHRG
	    CHECK (CH_CHRG >= 0)
IF NOT EXISTS ( SELECT name FROM sys.check_constraints Where name = 'CK_CH_C_TIER' )
    ALTER TABLE CHARGE ADD CONSTRAINT CK_CH_C_TIER
	    CHECK (CH_C_TIER in (1,2,3))
GO

-- *** COMMISSION_RATE ***
IF NOT EXISTS ( SELECT name FROM sys.check_constraints Where name = 'CK_CR_C_TIER' )
    ALTER TABLE COMMISSION_RATE ADD CONSTRAINT CK_CR_C_TIER
    	CHECK (CR_C_TIER in (1,2,3))
IF NOT EXISTS ( SELECT name FROM sys.check_constraints Where name = 'CK_CR_FROM_QTY' )
    ALTER TABLE COMMISSION_RATE ADD CONSTRAINT CK_CR_FROM_QTY
	    CHECK (CR_FROM_QTY >= 0)
IF NOT EXISTS ( SELECT name FROM sys.check_constraints Where name = 'CK_CR_TO_QTY' )
    ALTER TABLE COMMISSION_RATE ADD CONSTRAINT CK_CR_TO_QTY
    	CHECK (CR_TO_QTY > CR_FROM_QTY)
IF NOT EXISTS ( SELECT name FROM sys.check_constraints Where name = 'CK_CR_RATE' )
    ALTER TABLE COMMISSION_RATE ADD CONSTRAINT CK_CR_RATE
	    CHECK (CR_RATE >= 0)
GO

-- *** TAXRATE ***
IF NOT EXISTS ( SELECT name FROM sys.check_constraints Where name = 'CK_TX_RATE' )
    ALTER TABLE TAXRATE ADD CONSTRAINT CK_TX_RATE
	    CHECK (TX_RATE >= 0)
GO

-- *** TRADE_TYPE *** 
IF NOT EXISTS ( SELECT name FROM sys.check_constraints Where name = 'CK_TRADE_TYPE_IS_SELL' )
    ALTER TABLE TRADE_TYPE ADD CONSTRAINT CK_TRADE_TYPE_IS_SELL
	    CHECK (TT_IS_SELL in (0, 1))
IF NOT EXISTS ( SELECT name FROM sys.check_constraints Where name = 'CK_TRADE_TYPE_IS_MRKT' )
    ALTER TABLE TRADE_TYPE ADD CONSTRAINT CK_TRADE_TYPE_IS_MRKT
	    CHECK (TT_IS_MRKT in (0, 1))
GO
