I am trying to Create a foreign key table using the tables below. I also have pr
ID: 3914334 • Letter: I
Question
I am trying to Create a foreign key table using the tables below. I also have provided an example in bold of the format I am looking for..
-------------------------------------------------------------------
-- Child Parent Column(s)
-- ----- ------ ---------
-- 1 TStoreCashiers TStores intStoreID
-- 2 TStoreCashiers TCashiers intCashierID
-- 1
ALTER TABLE TStoreCashiers ADD CONSTRAINT TStoreCashiers_TStores_FK
FOREIGN KEY ( intStoreID ) REFERENCES TStores ( intStoreID );
-- 2
ALTER TABLE TStoreCashiers ADD CONSTRAINT TStoreCashiers_TCashiers_FK
FOREIGN KEY ( intCashierID ) REFERENCES TCashiers ( intCashierID );
->> please note that * = PK for that tabe
TStudies
intStudyID* (1,2)
,strStudyDesc
TSites
intSiteID * (1,2,3,4, etc.)
,intSiteNumber (101, 102, 103, etc.)
,intStudyID
,strName
,strAddress
,strCity
,strState
,strZip
,strPhone
TPatients
intPatientID * (1,2,3,4, etc.)
,intPatientNumber (101001, 102001, etc)
,intSiteID
,dtmDOB
,intGenderID
,intWeight
,intRandomCodeID allow Nulls
TVisitTypes
intVisitTypeID * (1,2,3)
,strVisitDesc (Screening, Randomization, Withdrawal)
TVisits
intVisitID * (1,2,3,etc.)
,intPatientID
,dtmVisit
,intVisitTypeID
,intWithdrawReasonID allow Nulls
TRandomCodes
intRandomCodeID * (1,2,3,4, etc.)
,intRandomCode (1000, 1001, 1002, etc.)
,intStudyID
,strTreatment (A-active or P-placebo)
,blnAvailable (T or F) use a varchar data type
TDrugKits
intDrugKitID * (1,2,3,4, etc.)
intDrugKitNumber (10000, 10001, 10002, etc.)
,intSiteID
,strTreatment (A-active or P-placebo)
,intVisitID (if a Visit ID entered it is already assigned and therefore not available) allow Nulls
TWithdrawReasons
intWithdrawReasonID * (1,2,3,etc.)
,strWithdrawDesc
TGenders
intGenderID* (1,2)
strGender
any help would be greatly apprecaited!
Explanation / Answer
I have created the tables using the description that given in the question and also added the relationship based on the PK and FK
Below is the alter script for adding foreign key to the tables and i have added them in the format as you suggested
S.No
Child
Parent
Column(s)
1
TDrugKits
TSites
intSiteID
2
TDrugKits
TVisits
intVisitID
3
TPatients
TGenders
intGenderID
4
TPatients
TRandomCodes
intRandomCodeID
5
TPatients
TSites
intSiteID
6
TRandomCodes
TStudies
intStudyID
7
TSites
TStudies
intStudyID
8
TVisits
TPatients
intPatientID
9
TVisits
TVisitTypes
intVisitTypeID
10
TVisits
TWithdrawReasons
intWithdrawReasonID
1--
ALTER TABLE TDrugKits WITH CHECK ADD CONSTRAINT FK_TDrugKits_TSites FOREIGN KEY(intSiteID)
REFERENCES TSites (intSiteID);
2--
ALTER TABLE TDrugKits WITH CHECK ADD CONSTRAINT FK_TDrugKits_TVisits FOREIGN KEY(intVisitID)
REFERENCES TVisits (intVisitID);
3--
ALTER TABLE TPatients WITH CHECK ADD CONSTRAINT FK_TPatients_TGenders FOREIGN KEY(intGenderID)
REFERENCES TGenders (intGenderID);
4--
ALTER TABLE TPatients WITH CHECK ADD CONSTRAINT FK_TPatients_TRandomCodes FOREIGN KEY(intRandomCodeID)
REFERENCES TRandomCodes (intRandomCodeID);
5--
ALTER TABLE TPatients WITH CHECK ADD CONSTRAINT FK_TPatients_TSites FOREIGN KEY(intSiteID)
REFERENCES TSites (intSiteID);
6--
ALTER TABLE TRandomCodes WITH CHECK ADD CONSTRAINT FK_TRandomCodes_TStudies FOREIGN KEY(intStudyID)
REFERENCES TStudies (intStudyID);
7--
ALTER TABLE TSites WITH CHECK ADD CONSTRAINT FK_TSites_TStudies FOREIGN KEY(intStudyID)
REFERENCES TStudies (intStudyID);
8--
ALTER TABLE TVisits WITH CHECK ADD CONSTRAINT FK_TVisits_TPatients FOREIGN KEY(intPatientID)
REFERENCES TPatients (intPatientID);
9--
ALTER TABLE TVisits WITH CHECK ADD CONSTRAINT FK_TVisits_ TVisitTypes FOREIGN KEY(intVisitTypeID)
REFERENCES TVisitTypes (intVisitTypeID);
10--
ALTER TABLE TVisits WITH CHECK ADD CONSTRAINT FK_TVisits_TWithdrawReasons FOREIGN KEY(intWithdrawReasonID)
REFERENCES TWithdrawReasons (intWithdrawReasonID);
And if needed you can use the below DDL script for creating the tables in the database
**********************************Create table Script********************************
TDrugKits
CREATE TABLE TDrugKits(
intDrugKitID int IDENTITY(1,1) NOT NULL,
intDrugKitNumber int NOT NULL,
intSiteID int NOT NULL,
strTreatment varchar(150) NOT NULL,
intVisitID int NULL,
PRIMARY KEY (intDrugKitID))
TGenders
CREATE TABLE TGenders(
intGenderID int IDENTITY(1,1) NOT NULL,
strGender varchar(50) NOT NULL,
PRIMARY KEY (intGenderID))
TPatients
CREATE TABLE TPatients(
intPatientID int IDENTITY(1,1) NOT NULL,
intPatientNumber int NOT NULL,
intSiteID int NOT NULL,
dtmDOB datetime NOT NULL,
intGenderID int NOT NULL,
intWeight int NOT NULL,
intRandomCodeID int NULL,
PRIMARY KEY (intPatientID))
TRandomCodes
CREATE TABLE TRandomCodes(
intRandomCodeID int IDENTITY(1,1) NOT NULL,
intRandomCode int NOT NULL,
intStudyID int NOT NULL,
strTreatment varchar(150) NOT NULL,
blnAvailable varchar(50) NOT NULL,
PRIMARY KEY (intRandomCodeID))
TSites
CREATE TABLE TSites(
intSiteID int IDENTITY(1,1) NOT NULL,
intSiteNumber int NOT NULL,
intStudyID int NOT NULL,
strName varchar(100) NOT NULL,
strAddress varchar(150) NOT NULL,
strCity varchar(100) NOT NULL,
strState varchar(100) NOT NULL,
strZip varchar(10) NOT NULL,
strPhone varchar(20) NOT NULL,
PRIMARY KEY (intSiteID))
TStudies
CREATE TABLE TStudies(
intStudyID int IDENTITY(1,1) NOT NULL,
strStudyDesc varchar(100) NOT NULL,
PRIMARY KEY (intStudyID))
TVisits
CREATE TABLE TVisits(
intVisitID int IDENTITY(1,1) NOT NULL,
intPatientID int NOT NULL,
dtmVisit datetime NOT NULL,
intVisitTypeID int NOT NULL,
intWithdrawReasonID int NULL,
PRIMARY KEY (intVisitID))
TVisitTypes
CREATE TABLE TVisitTypes(
intVisitTypeID int IDENTITY(1,1) NOT NULL,
strVisitDesc varchar(200) NOT NULL,
PRIMARY KEY (intVisitTypeID))
TWithdrawReasons
CREATE TABLE TWithdrawReasons(
intWithdrawReasonID int IDENTITY(1,1) NOT NULL,
strWithdrawDesc varchar(150) NOT NULL,
PRIMARY KEY (intWithdrawReasonID))
***********************************END***************************************
S.No
Child
Parent
Column(s)
1
TDrugKits
TSites
intSiteID
2
TDrugKits
TVisits
intVisitID
3
TPatients
TGenders
intGenderID
4
TPatients
TRandomCodes
intRandomCodeID
5
TPatients
TSites
intSiteID
6
TRandomCodes
TStudies
intStudyID
7
TSites
TStudies
intStudyID
8
TVisits
TPatients
intPatientID
9
TVisits
TVisitTypes
intVisitTypeID
10
TVisits
TWithdrawReasons
intWithdrawReasonID
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.