SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 I can't figure out how to insert into this
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zakzak
Starting Member

USA
1 Posts

Posted - 09/30/2013 :  23:14:57  Show Profile  Reply with Quote
I thought i messed up the last foreign key constraint then i started changing things now I'm really lost.
-- --------------------------------------------------------------------------------
-- Step #2.1: Create Tables
-- --------------------------------------------------------------------------------
CREATE TABLE TPatients
(
	 intPatientID							INTEGER			NOT NULL
	,strFirstName							VARCHAR(50)		NOT NULL
	,strLastName							VARCHAR(50)		NOT NULL
	,CONSTRAINT TPatients_PK PRIMARY KEY ( intPatientID )
)

CREATE TABLE TPatientVisits
(
	 intPatientID							INTEGER			NOT NULL
	,intVisitIndex							INTEGER			NOT NULL
	,dtmVisitDate							DATE			NOT NULL
	,strVisitNotes							VARCHAR(8000)	NOT NULL
	,CONSTRAINT TPatientVisits_PK PRIMARY KEY ( intPatientID, intVisitIndex )
)

CREATE TABLE TVisitProcedures
(	 
	 intPatientID							INTEGER			NOT NULL
	,intVisitIndex							INTEGER			NOT NULL
	,intProcedureIndex						INTEGER			NOT NULL
	,strProcedureName						VARCHAR(50)		NOT NULL
	,CONSTRAINT TVisitProcedures_PK PRIMARY KEY ( intVisitIndex, intProcedureIndex )
)

CREATE TABLE TVisitMedications
(
	 intPatientID							INTEGER			NOT NULL
	,intVisitIndex							INTEGER			NOT NULL
	,intMedicationIndex						INTEGER			NOT NULL
	,strMedicationName						VARCHAR(50)		NOT NULL
	,CONSTRAINT TVisitMedications_PK PRIMARY KEY ( intVisitIndex, intMedicationIndex )
)

CREATE TABLE TVisitLabTests
(
	 intPatientID							INTEGER			NOT NULL
	,intVisitIndex							INTEGER			NOT NULL
	,intLabTestIndex						INTEGER			NOT NULL
	,intResultIndex							INTEGER			NOT NULL
	,strLabTestName							VARCHAR(50)		NOT NULL
	,CONSTRAINT TVisitLabTests_PK PRIMARY KEY ( intVisitIndex, intLabTestIndex )
)

CREATE TABLE TLabTestResults
(
	 intVisitIndex							INTEGER			NOT NULL
	,intLabTestIndex						INTEGER			NOT NULL
	,intResultIndex							INTEGER			NOT NULL
	,strResultName							VARCHAR(50)		NOT NULL
	,CONSTRAINT TLabTestResults_PK PRIMARY KEY ( intVisitIndex, intLabTestIndex, intResultIndex )
)

-- --------------------------------------------------------------------------------
-- Step #2.2: Identify and Create Foreign Keys
-- --------------------------------------------------------------------------------
--
-- #	Child					Parent			Column(s)
--		-----					------			---------
-- 1	TPatientVisits			TPatients		intPatientID
-- 2	TVisitProcedures		TPatientVisits	intVisitIndex
-- 3	TVisitMedications		TPatientVisits	intVisitIndex
-- 4	TVisitLabTests			TPatientVisits	intVisitIndex
-- 5	TLabTestResults			TVisitLabTests	intLabTestIndex

-- 1
ALTER TABLE TPatientVisits ADD CONSTRAINT TPatientVisits_TPatients_FK
FOREIGN KEY ( intPatientID ) REFERENCES TPatients ( intPatientID )

-- 2
ALTER TABLE TVisitProcedures ADD CONSTRAINT TVisitProcedures_TPatientVisits_FK
FOREIGN KEY ( intPatientID,intVisitIndex ) REFERENCES TPatientVisits ( intPatientID,intVisitIndex )

-- 3
ALTER TABLE TVisitMedications ADD CONSTRAINT TVisitMedications_TPatientVisits_FK
FOREIGN KEY ( intPatientID,intVisitIndex ) REFERENCES TPatientVisits ( intPatientID,intVisitIndex )

-- 4
ALTER TABLE TVisitLabTests ADD CONSTRAINT TVisitLabTests_TPatientVisits_FK
FOREIGN KEY ( intPatientID,intVisitIndex ) REFERENCES TPatientVisits ( intPatientID,intVisitIndex )

---- 5 -- I was slightly obssesed to figure out this constraint I hope I got it right!
--ALTER TABLE TLabTestResults ADD CONSTRAINT TLabTestResults_TVisitLabTests_FK
--FOREIGN KEY ( intLabTestIndex ) REFERENCES TVisitLabTests ( intLabTestIndex )

-- --------------------------------------------------------------------------------
-- Step #2.3: Add at least 2 patients
-- --------------------------------------------------------------------------------
INSERT INTO TPatients( intPatientID, strFirstName, strLastName )
VALUES		 (1,'Mary','Hagert')
			,(2,'Bob','Dylan')

-- --------------------------------------------------------------------------------
-- Step #2.4: Add at least 2 visits per patient
-- --------------------------------------------------------------------------------
INSERT INTO TPatientVisits( 
							 intPatientID
							,intVisitIndex
							,dtmVisitDate
							,strVisitNotes
						    )
VALUES		 (1,1,'09/25/2013','Patient complained of tiredness and fatigue')
			,(1,2,'09/29/2013','Patient responded well to extra sleep and coffee')
			,(2,1,'09/25/2013','Patient and I go WAY back ol timer all the way back to the boot scoot..')
			,(2,2,'09/25/2013','To whom ever that is making funny in my notes better knock it off!')

-- --------------------------------------------------------------------------------
-- Step #2.5: Add at least 2 procedures per patient
-- --------------------------------------------------------------------------------
INSERT INTO TVisitProcedures(
							 intPatientID
							,intVisitIndex
							,intProcedureIndex
							,strProcedureName
							)
VALUES		 (1, 1, 1,'The embarassing one')
			,(1, 1, 4,'NO This one is even worse!')
			,(1, 2, 8,'Okay you got me.. This is the one..')
			,(1, 2, 12,'Nothing to see here everyone just go home...')
			,(2, 1, 2,'Check blood pressure')
			,(2, 1, 5,'Check blood sugar')
			,(2, 2, 9,'Obamacare drug screen')
			,(2, 2, 13,'Just kidding great idea, purposely designed to fail policy so we can continue SSDD..')

-- --------------------------------------------------------------------------------
-- Step #2.6: Add at least 2 medications per patient visit
-- --------------------------------------------------------------------------------
INSERT INTO TVisitMedications(
							 intPatientID
							,intVisitIndex
							,intMedicationIndex
							,strMedicationName
							)
VALUES		 (1, 1, 1,'This is illegal ya know')
			,(1, 1, 4,'Setting quotas on the pills are we?')
			,(1, 2, 8,'Okay just cut me in')
			,(1, 2, 12,'What! The margins are to small you quit?')
			,(2, 1, 2,'Adderall')
			,(2, 1, 5,'Xanax')
			,(2, 2, 9,'Hydroxyzine Pamoate')
			,(2, 2, 13,'Alcohol')

-- --------------------------------------------------------------------------------
-- Step #2.7: Add at least 2 lab tests per patient visit
-- --------------------------------------------------------------------------------
INSERT INTO TVisitLabTests(
							 intPatientID
							,intVisitIndex
							,intLabTestIndex
							,intResultIndex
							,strLabTestName
							)
VALUES		 (1, 1, 1, 1, 'Hemoglobin count')
			,(1, 1, 4, 1, 'Iron count')
			,(1, 2, 8, 1, 'Allergy screen')
			,(1, 2, 12,1, 'Skin biopsy')
			,(2, 1, 2, 1, 'Blood Type')
			,(2, 1, 5, 1, 'Electrolytes')
			,(2, 2, 9, 1, 'Enzymes & Proteins')
			,(2, 2, 13,1, 'Lipid blood test')

-- --------------------------------------------------------------------------------
-- Step #2.8: Add at least 2 results per lab tests
-- --------------------------------------------------------------------------------
INSERT INTO TLabTestResults(
							 intVisitIndex
							,intLabTestIndex
							,intResultIndex
							,strResultName
							)
VALUES		 (1, 1, 1,'Without a doubt')
			,(1, 1, 4,'Yes definitely')
			,(1, 2, 8,'Most likely')
			,(1, 2, 12,'Signs point to yes')
			,(2, 1, 2,'Outlook not certain')
			,(2, 1, 5,'It is certain')
			,(2, 2, 9,'Reply hazy try again')
			,(2, 2, 13,'Cannot predict now')

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1677 Posts

Posted - 10/01/2013 :  19:10:42  Show Profile  Reply with Quote
Can you state the issue? What are you trying to do? What happens when you try to do it? What would a good outcome look like?

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000