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

---
--- Set connection attributes
---
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

-- *** ACCOUNT_PERMISSION ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_ACCOUNT_PERMISSION_CUSTOMER_ACCOUNT' )
    ALTER TABLE ACCOUNT_PERMISSION WITH NOCHECK ADD CONSTRAINT FK_ACCOUNT_PERMISSION_CUSTOMER_ACCOUNT     
        FOREIGN KEY (AP_CA_ID) REFERENCES CUSTOMER_ACCOUNT(CA_ID)
GO

-- *** ADDRESS ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_ADDRESS_ZIP_CODE' )
    ALTER TABLE ADDRESS WITH NOCHECK ADD CONSTRAINT FK_ADDRESS_ZIP_CODE
        FOREIGN KEY (AD_ZC_CODE) REFERENCES ZIP_CODE(ZC_CODE)
GO

-- *** BROKER ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_BROKER_STATUS' )
    ALTER TABLE BROKER WITH NOCHECK ADD CONSTRAINT FK_BROKER_STATUS
        FOREIGN KEY (B_ST_ID) REFERENCES STATUS_TYPE(ST_ID)
GO

-- *** CASH_TRANSACTION ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_CASH_TRANSACTION_TRADE' )
    ALTER TABLE CASH_TRANSACTION WITH NOCHECK ADD CONSTRAINT FK_CASH_TRANSACTION_TRADE
        FOREIGN KEY (CT_T_ID) REFERENCES TRADE(T_ID)
GO

-- *** CHARGE ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_CHARGE_TRADETYPE' )
    ALTER TABLE CHARGE WITH NOCHECK ADD CONSTRAINT FK_CHARGE_TRADETYPE
        FOREIGN KEY (CH_TT_ID) REFERENCES TRADE_TYPE(TT_ID)
GO

-- *** COMMISSION_RATE ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_COMMISSION_RATE_TRADETYPE' )
    ALTER TABLE COMMISSION_RATE WITH NOCHECK ADD CONSTRAINT FK_COMMISSION_RATE_TRADETYPE
        FOREIGN KEY (CR_TT_ID) REFERENCES TRADE_TYPE(TT_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_COMMISSION_RATE_EXCHANGE' )
    ALTER TABLE COMMISSION_RATE WITH NOCHECK ADD CONSTRAINT FK_COMMISSION_RATE_EXCHANGE
        FOREIGN KEY (CR_EX_ID) REFERENCES EXCHANGE(EX_ID)
GO

-- *** COMPANY ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_COMPANY_STATUS' )
    ALTER TABLE COMPANY WITH NOCHECK ADD CONSTRAINT FK_COMPANY_STATUS
        FOREIGN KEY (CO_ST_ID) REFERENCES STATUS_TYPE(ST_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_COMPANY_ADDRESS' )
    ALTER TABLE COMPANY WITH NOCHECK ADD CONSTRAINT FK_COMPANY_ADDRESS
        FOREIGN KEY (CO_AD_ID) REFERENCES ADDRESS(AD_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_COMPANY_INDUSTRY' )
    ALTER TABLE COMPANY WITH NOCHECK ADD CONSTRAINT FK_COMPANY_INDUSTRY
        FOREIGN KEY (CO_IN_ID) REFERENCES INDUSTRY(IN_ID)
GO

-- *** COMPANY_COMPETITOR ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_COMPANY_COMP_COMPANY1' )
    ALTER TABLE COMPANY_COMPETITOR WITH NOCHECK ADD CONSTRAINT FK_COMPANY_COMP_COMPANY1
        FOREIGN KEY (CP_CO_ID) REFERENCES COMPANY(CO_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_COMPANY_COMP_COMPANY2' )
    ALTER TABLE COMPANY_COMPETITOR WITH NOCHECK ADD CONSTRAINT FK_COMPANY_COMP_COMPANY2
        FOREIGN KEY (CP_COMP_CO_ID) REFERENCES COMPANY(CO_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_COMPANY_COMP_INDUSTRY' )
    ALTER TABLE COMPANY_COMPETITOR WITH NOCHECK ADD CONSTRAINT FK_COMPANY_COMP_INDUSTRY
        FOREIGN KEY (CP_IN_ID) REFERENCES INDUSTRY(IN_ID)
GO

-- *** CUSTOMER ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_CUSTOMER_STATUS_TYPE' )
    ALTER TABLE CUSTOMER WITH NOCHECK ADD CONSTRAINT FK_CUSTOMER_STATUS_TYPE
        FOREIGN KEY (C_ST_ID) REFERENCES STATUS_TYPE(ST_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_CUSTOMER_ADDRESS' )
    ALTER TABLE CUSTOMER WITH NOCHECK ADD CONSTRAINT FK_CUSTOMER_ADDRESS
        FOREIGN KEY (C_AD_ID) REFERENCES ADDRESS(AD_ID)
GO

-- *** CUSTOMER_ACCOUNT ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_CUSTOMER_ACCOUNT_BROKER' )
    ALTER TABLE CUSTOMER_ACCOUNT WITH NOCHECK ADD CONSTRAINT FK_CUSTOMER_ACCOUNT_BROKER
        FOREIGN KEY (CA_B_ID) REFERENCES BROKER(B_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_CUSTOMER_ACCOUNT_CUSTOMER' )
    ALTER TABLE CUSTOMER_ACCOUNT WITH NOCHECK ADD CONSTRAINT FK_CUSTOMER_ACCOUNT_CUSTOMER
        FOREIGN KEY (CA_C_ID) REFERENCES CUSTOMER(C_ID)
GO

-- *** CUSTOMER_TAXRATE ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_CUSTOMER_TAXRATE_TAX' )
    ALTER TABLE CUSTOMER_TAXRATE WITH NOCHECK ADD CONSTRAINT FK_CUSTOMER_TAXRATE_TAX
        FOREIGN KEY (CX_TX_ID) REFERENCES TAXRATE(TX_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_CUSTOMER_TAXRATE_CUSTOMER' )
    ALTER TABLE CUSTOMER_TAXRATE WITH NOCHECK ADD CONSTRAINT FK_CUSTOMER_TAXRATE_CUSTOMER
        FOREIGN KEY (CX_C_ID) REFERENCES CUSTOMER(C_ID)
GO

-- *** DAILY_MARKET ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_DAILY_MARKET_SECURITY' )
    ALTER TABLE DAILY_MARKET WITH NOCHECK ADD CONSTRAINT FK_DAILY_MARKET_SECURITY
        FOREIGN KEY (DM_S_SYMB) REFERENCES SECURITY(S_SYMB)
GO

-- *** EXCHANGE ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_EXCHANGE_ADDRESS' )
    ALTER TABLE EXCHANGE WITH NOCHECK ADD CONSTRAINT FK_EXCHANGE_ADDRESS
        FOREIGN KEY (EX_AD_ID) REFERENCES ADDRESS(AD_ID)
GO

-- *** FINANCIAL ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_FINANCIAL_COMPANY' )
    ALTER TABLE FINANCIAL WITH NOCHECK ADD CONSTRAINT FK_FINANCIAL_COMPANY
        FOREIGN KEY (FI_CO_ID) REFERENCES COMPANY(CO_ID)
GO

-- *** INDUSTRY ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_INDUSTRY_SECTOR' )
    ALTER TABLE INDUSTRY WITH NOCHECK ADD CONSTRAINT FK_INDUSTRY_SECTOR
        FOREIGN KEY (IN_SC_ID) REFERENCES SECTOR(SC_ID)
GO
-- *** LAST_TRADE ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_LAST_TRADE_SECURITY' )
    ALTER TABLE LAST_TRADE WITH NOCHECK ADD CONSTRAINT FK_LAST_TRADE_SECURITY
        FOREIGN KEY (LT_S_SYMB) REFERENCES SECURITY(S_SYMB)
GO

-- *** HOLDING ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_HOLDING_TRADE' )
    ALTER TABLE HOLDING WITH NOCHECK ADD CONSTRAINT FK_HOLDING_TRADE
        FOREIGN KEY (H_T_ID) REFERENCES TRADE(T_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_HOLDING_HOLDING_SUMMARY')
    ALTER TABLE HOLDING WITH NOCHECK ADD CONSTRAINT FK_HOLDING_HOLDING_SUMMARY
        FOREIGN KEY (H_CA_ID, H_S_SYMB) REFERENCES HOLDING_SUMMARY (HS_CA_ID, HS_S_SYMB)
GO

-- *** HOLDING_HISTORY ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_HOLDING_HISTORY_TRADE1' )
    ALTER TABLE HOLDING_HISTORY WITH NOCHECK ADD CONSTRAINT FK_HOLDING_HISTORY_TRADE1
        FOREIGN KEY (HH_H_T_ID) REFERENCES TRADE(T_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_HOLDING_HISTORY_TRADE2' )
    ALTER TABLE HOLDING_HISTORY WITH NOCHECK ADD CONSTRAINT FK_HOLDING_HISTORY_TRADE2
        FOREIGN KEY (HH_T_ID) REFERENCES TRADE(T_ID)
GO

-- *** HOLDING_SUMMARY ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_HOLDING_SUMMARY_ACCOUNT' )
    ALTER TABLE HOLDING_SUMMARY WITH NOCHECK ADD CONSTRAINT FK_HOLDING_SUMMARY_ACCOUNT
        FOREIGN KEY (HS_CA_ID) REFERENCES CUSTOMER_ACCOUNT(CA_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_HOLDING_SUMMARY_SECURITY' )
    ALTER TABLE HOLDING_SUMMARY WITH NOCHECK ADD CONSTRAINT FK_HOLDING_SUMMARY_SECURITY
        FOREIGN KEY (HS_S_SYMB) REFERENCES SECURITY(S_SYMB)
GO

-- *** NEWS_XREF ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_NEWS_XREF_NEWS_ITEM' )
    ALTER TABLE NEWS_XREF WITH NOCHECK ADD CONSTRAINT FK_NEWS_XREF_NEWS_ITEM
        FOREIGN KEY (NX_NI_ID) REFERENCES NEWS_ITEM(NI_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_NEWS_XREF_COMPANY' )
    ALTER TABLE NEWS_XREF WITH NOCHECK ADD CONSTRAINT FK_NEWS_XREF_COMPANY
        FOREIGN KEY (NX_CO_ID) REFERENCES COMPANY(CO_ID)
GO

-- *** SECURITY ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_SECURITY_STATUS' )
    ALTER TABLE SECURITY WITH NOCHECK ADD CONSTRAINT FK_SECURITY_STATUS
        FOREIGN KEY (S_ST_ID) REFERENCES STATUS_TYPE(ST_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_SECURITY_EXCHANGE' )
    ALTER TABLE SECURITY WITH NOCHECK ADD CONSTRAINT FK_SECURITY_EXCHANGE
        FOREIGN KEY (S_EX_ID) REFERENCES EXCHANGE(EX_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_SECURITY_COMPANY' )
    ALTER TABLE SECURITY WITH NOCHECK ADD CONSTRAINT FK_SECURITY_COMPANY
        FOREIGN KEY (S_CO_ID) REFERENCES COMPANY(CO_ID)
GO

-- *** SETTLEMENT ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_SETTLEMENT_TRADE' )
    ALTER TABLE SETTLEMENT WITH NOCHECK ADD CONSTRAINT FK_SETTLEMENT_TRADE
        FOREIGN KEY (SE_T_ID) REFERENCES TRADE(T_ID)
GO

-- *** TRADE ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_STATUS' )
    ALTER TABLE TRADE WITH NOCHECK ADD CONSTRAINT FK_TRADE_STATUS
        FOREIGN KEY (T_ST_ID) REFERENCES STATUS_TYPE(ST_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_TRADETYPE' )
    ALTER TABLE TRADE WITH NOCHECK ADD CONSTRAINT FK_TRADE_TRADETYPE
        FOREIGN KEY (T_TT_ID) REFERENCES TRADE_TYPE(TT_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_SECURITY' )
    ALTER TABLE TRADE WITH NOCHECK ADD CONSTRAINT FK_TRADE_SECURITY
        FOREIGN KEY (T_S_SYMB) REFERENCES SECURITY(S_SYMB)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_CUSTOMER_ACCOUNT' )
    ALTER TABLE TRADE WITH NOCHECK ADD CONSTRAINT FK_TRADE_CUSTOMER_ACCOUNT
        FOREIGN KEY (T_CA_ID) REFERENCES CUSTOMER_ACCOUNT(CA_ID)
GO

-- *** TRADE_HISTORY ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_HISTORY_TRADE ' )
    ALTER TABLE TRADE_HISTORY WITH NOCHECK ADD CONSTRAINT FK_TRADE_HISTORY_TRADE 
        FOREIGN KEY (TH_T_ID) REFERENCES TRADE(T_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_HISTORY_STATUS ' )
    ALTER TABLE TRADE_HISTORY WITH NOCHECK ADD CONSTRAINT FK_TRADE_HISTORY_STATUS 
        FOREIGN KEY (TH_ST_ID) REFERENCES STATUS_TYPE(ST_ID)
GO

-- *** TRADE_REQUEST ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_REQUEST_TRADE ' )
    ALTER TABLE TRADE_REQUEST WITH NOCHECK ADD CONSTRAINT FK_TRADE_REQUEST_TRADE 
        FOREIGN KEY (TR_T_ID) REFERENCES TRADE(T_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_REQUEST_TRADETYPE ' )
    ALTER TABLE TRADE_REQUEST WITH NOCHECK ADD CONSTRAINT FK_TRADE_REQUEST_TRADETYPE 
        FOREIGN KEY (TR_TT_ID) REFERENCES TRADE_TYPE(TT_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_REQUEST_SECURITY ' )
    ALTER TABLE TRADE_REQUEST WITH NOCHECK ADD CONSTRAINT FK_TRADE_REQUEST_SECURITY 
        FOREIGN KEY (TR_S_SYMB) REFERENCES SECURITY(S_SYMB)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_TRADE_REQUEST_BROKER' )
    ALTER TABLE TRADE_REQUEST WITH NOCHECK ADD CONSTRAINT FK_TRADE_REQUEST_BROKER
        FOREIGN KEY (TR_B_ID) REFERENCES BROKER(B_ID)
GO

-- *** WATCH_ITEM ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_WATCH_ITEM_WATCHLIST' )
    ALTER TABLE WATCH_ITEM WITH NOCHECK ADD CONSTRAINT FK_WATCH_ITEM_WATCHLIST
        FOREIGN KEY (WI_WL_ID) REFERENCES WATCH_LIST(WL_ID)
GO
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_WATCH_ITEM_SECURITY' )
    ALTER TABLE WATCH_ITEM WITH NOCHECK ADD CONSTRAINT FK_WATCH_ITEM_SECURITY
        FOREIGN KEY (WI_S_SYMB) REFERENCES SECURITY(S_SYMB)
GO

-- *** WATCH_LIST ***
IF NOT EXISTS ( SELECT name FROM sysobjects WHERE name = 'FK_WATCH_LIST_CUSTOMER' )
    ALTER TABLE WATCH_LIST WITH NOCHECK ADD CONSTRAINT FK_WATCH_LIST_CUSTOMER
        FOREIGN KEY (WL_C_ID) REFERENCES CUSTOMER(C_ID)
GO
