Sample SQL Script to Create table with Foreign Key Constraints

Step 1 :  Creating the tables

drop table SecurityMaster

Create Table SecurityMaster
(
SecurityMasterID int not null identity constraint SecurityMaster_PKSecurityMasterID primary key,
CurrentPurchaseLimit Decimal,
FacilityFee varchar(255),
BasePercentage decimal,
UsedProgram varchar(255),
LCFee decimal,
BasePercentage2 decimal,
SettlementPaymentDateSD date,
SettlementPaymentDateSDType int,
LAFAPool varchar(255),
CalculationCDNextBusinessDay varchar(255),
CalculationCDNextBusinessDayType int,
GRID varchar(255),
CalculationDate datetime,
PaymentDate datetime,
ExpiryDate datetime,
InvoiceDueDate datetime,
FeeToParis decimal,
AdminFee decimal,
EstimatedActualLIBOR decimal,
EstimatedActualLIBORType int,
AmortMatchFounderDeals varchar(255),
AmortMatchFundedDealTypeID int,
Analyst varchar(255),
PM varchar(255),
KeyContacts varchar(255),
CurrentMonthDealStatus varchar(255),
CurrentMonthRenewalEffectiveDate datetime,
YTDDealStatus varchar(255),
YTDRenewalEffectiveDate DateTime
)

alter table SecurityMaster

add foreign key(SettlementPaymentDateSD)
references LK_SettlementPaymentDate(LK_SettlementPaymentDateID)


create table LK_SettlementPaymentDate
(
LK_SettlementPaymentDateID integer identity not null constraint LK_SettlementPaymentDateID primary key,
Description varchar(255)
)

create table LK_EstimatedActualLIBORType
(
LK_EstimatedActualLIBORTypeID integer identity not null
constraint LK_EstimatedActualLIBORTypeID primary key,
Description varchar(255)
)

create table LK_CalculationCDNextSettlementPaymentDate
(
LK_CalculationCDNextSettlementPaymentDateID integer identity not null constraint LK_CalculationCDNextSettlementPaymentDateID primary key,
Description varchar(255)
)


drop table LK_AmountMatchFundedDeal

drop table LK_AmortMatchFoundedDealTypeID

create table LK_AmortMatchFundedDealTypeID
(
LK_AmortMatchFundedDealTypeID integer identity not null constraint LK_AmortMatchFundedDealTypeID_PK primary key,
Description varchar(255)
)

Step 2:  Adding Foreign Key Constraints

alter table SecurityMaster

add foreign key(SettlementPaymentDateSDType)

references LK_SettlementPaymentDate(LK_SettlementPaymentDateID)

-- AmortMATCHFUNDED Deal

alter table SecurityMaster

add foreign key(EstimatedActualLIBORType)

references LK_EstimatedActualLIBORType(LK_EstimatedActualLIBORTypeID)

-- AmortMATCHFUNDED Deal

alter table SecurityMaster

add foreign key(AmortMatchFundedDealTypeID)

references LK_AmortMatchFundedDealTypeID(LK_AmortMatchFundedDealTypeID)