April 16, 2014 at 5:19 pm
So i have a project for school that i need to make an SQL server. I created the note pad file for all the tables and i think i have all the keys i need but i can't figure out how i am supposed to build the relationships. Supposedly you can use the same PK for two tables but i thought that wasn't possible so i am really confused from top to bottom. So the project I have to take someone's DB design from visio and create it in SQL server 2008 from the virtual thing at school. I provided a link to my Dropbox folder with all the files. I really appreciate any help i can get.
https://www.dropbox.com/sh/y7t21cjav5wx6o7/Vfos1JHHxi
Thanks HF
April 16, 2014 at 6:20 pm
Can you post a really brief description of what you're modeling and your table definitions on the part you're having trouble with? (start with only maybe two or three tables). Once you get the hang of it, it gets a lot easier.
April 16, 2014 at 8:39 pm
Will you be on in 2-3 hours? I finally found someone at my school to help me so i might know what i am doing. In 2 to 3 hours i should have it done and it would be cool if you could just double check it to see if it was completed right. If the relationships are not 100% right it will take my grade from a C-D or if it is 100% right from a C-B so it is a huge turning point if i can get this right for tomorrow!
April 16, 2014 at 9:45 pm
seeing that it will be around 1 AM, probably not. But if you post a simple description of what you're modeling and then your model (create table scripts with the foreign keys in there), someone can help you.
Can you post a really simple description of what you're modeling? Start with two tables (parent and child) and work your way out. If you try doing everything at once, you're going to get really confused. (Been there, done that!) If you think you have it down, then add a few records to the two tables and see if you get the right answers from your query. (Add only enough records to verify your design is right - you can delete the records later.)
Hope that helps.
April 16, 2014 at 10:01 pm
YES!!!!!!! I figured it out and it loaded! Could you please look over it and see if everything is right....it loaded correctly but who knows.
Now for this I am curious if this is accurate.
Also are these two queries right? The answers are made up as an example of what SHOULD pop up.
"SELECT Employees.EmployeeID, Employees.Department, Location.City
FROM EMPLOYEES
INNER JOIN Location
ON
Employees.LocID = Location.LocID
Where Employees.Department = "Nurse" and Location.City = "DeKab";
1000 Nurse DeKalb
SELECT PatientRecords.PatientID, PatientRecords.FirstName, PatientRecords.LastName, PatientRecords.DateofBirthBirthday
FROM PatientRecords
WHERE PatientRecords.Firstname = "Kevin";
5 Kevin Smith 06271990
SELECT PatientRecords.PatientID, PatientRecords.FirstName, PatientRecords.LastName, PatientRecords.DateofBirthBirthday
FROM PatientRecords
WHERE PatientRecords.Enthicity = "White" and PatientRecords.City = "Chicago";
5 Kevin Smith 06271990
6 Bob Donald 08121983"
April 17, 2014 at 8:34 am
Without going into great detail you need to use the datetime datatype for dates. Using numeric(8) for dates is going to be a serious pain to deal with. I realize this is school work but DO NOT use anything other datetime for datetime data.
I would also suggest that using char for columns like Name is not the best choice. You should use varchar instead. There is no need to store a bunch of spaces. You will just have to trim the spaces every time you pull the data back out.
Proper datatype usage is critical to implementing a good database in the real world. Sadly, many people don't seem to understand that.
I suspect your professor will ask you why you have a unique constraint on FirstName, LastName. That prevents you from having more than 1 patient with the same name. Also, it prevents you from having the same contact for more than 1 patient. :w00t:
Some other questions, why do you have City, State, Zip in the employees table and the same columns in the Locations table? Shouldn't you have a Departments table instead of the department name in the Employees table?
Payments, shouldn't that be payments made against a Bill? You should not store the AmountBilled in the payments table. What happens if the AmountBilled needs to be changed? You have no solid way of updating however many payment rows there are.
Rooms should be a table. Then you would have a table for PatientRooms which would contain the PatientID, the RoomID and maybe Admission and Discharge dates.
To be fair, this looks like a very complicated project for a college student to put together. I don't know the actual assignment but hospital systems are incredibly complicated.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2014 at 11:37 am
ok here is the most updated one. and yea the dates are killing me. How would i do this date time thing? This should be a lot more updated. It loads and everything but when i insert my date i get the obvious date check issue and patient foreign key issue out the butt.
Also the reason why i used a lot of what i did was because i followed this one that was provided for my class....and yes this class is absurb. If you have 90%+ right the teacher gives you 50% #$^#$&#$& One missing relationship is like 25-50% off -_- I also attached the example files
Also the reason why it is like it is because the requirements were to make any type of database that would have 7-10 relationships, 7-10 tables, 16 records, 8 queries with 4 compound. This is like a semester long project.
So from what the directions are for the project you are looking too deep into it. We simply need to have it relate for the most part, be able to input data, and query it. It doesn't have to update perfectly and be 100% linked and stuff liek your saying but yea this project is a bit extreme
Lastly, i have been getting help from like 2 class mates so who knows if they know what they are doing -_- At least half the class is lost as can be.
"
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 134
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 141
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 162
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 169
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 176
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 183
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 190
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 204
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 211
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 218
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 225
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 232
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 239
The INSERT statement conflicted with the CHECK constraint "ValidHireDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.Employees", column 'HireDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 261
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 269
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 273
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 277
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 281
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 293
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 305
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 309
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 313
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 317
The INSERT statement conflicted with the CHECK constraint "ValidDateOfBirth". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'DateOfBirth'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 338
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 348
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 353
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 358
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 363
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 378
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 393
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 398
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 403
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 408
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 430
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 440
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 445
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 450
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 455
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 470
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 485
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 490
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 495
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 500
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK2". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 524
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 534
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 539
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 544
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 549
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 564
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 579
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 584
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 589
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 594
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK3". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 620
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 625
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 630
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 635
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 640
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 645
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 650
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 655
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 660
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 665
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 670
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 675
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 680
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 685
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 690
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 695
The INSERT statement conflicted with the CHECK constraint "ValidAdmissionDate". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTROOM", column 'AdmissionDate'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 714
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 724
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 729
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 734
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 739
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 754
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 769
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 774
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 779
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 784
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK5". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
(1 row(s) affected)
"
April 17, 2014 at 12:57 pm
utdc1 (4/17/2014)
ok here is the most updated one. and yea the dates are killing me. How would i do this date time thing?
You fix it by using datetime instead of numeric(8).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2014 at 1:43 pm
i don't have to actually fix any values? just the column? The monthdayyear can stay "01312000"
Also what is up with the patient foreign keys...what am i doing wrong? i have no idea. was messing with that for hours with a classmate
April 17, 2014 at 1:46 pm
utdc1 (4/17/2014)
i don't have to actually fix any values? just the column? The monthdayyear can stay "01312000"
You would have to add a dateformat to your script for this to work.
SET DATEFORMAT MDY
Also what is up with the patient foreign keys...what am i doing wrong? i have no idea. was messing with that for hours with a classmate
Dunno. I didn't look at all the errors.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2014 at 5:12 pm
You have inspired me to make my first post 🙂 Thank you!
So, as suggested, some of your issue lies in improper type usage. datetime is the way to go. Your initial create table script would look like this:
CREATE TABLE Employees (
....(clipped)...
ZipcodeNumeric(5)NOT NULL,
HireDate DatetimeNOT NULL,
LocIDIntNot Null,
CONSTRAINT EmployeePKPRIMARY KEY(EmployeeID)
/*CONSTRAINT ValidHireDate CHECK
(HireDate LIKE '[0-1][0-9][0-3][0-9][1-2][0-9][0-9][0-9]'),
*/ -- Remove this constraint entirely. If you fix the issue (the datatype) this becomes un-necessary, as the proper type is "valid" itself.
-- (Unless your teacher is forcing you to use an awkward datatype... gads, I hope not...)
);
Follow this generally for ALL of your "date" columns. type Datetime, and remove that constraint 🙂
Now, check this out 🙂 ALL of those problems go away. Syntactically, you are correct in your inserts.
So, if we look at one of the FOREIGN KEY errors:
Msg 547, Level 16, State 0, Line 338
The INSERT statement conflicted with the FOREIGN KEY constraint "PatientIDFK1". The conflict occurred in database "Z1725527", table "NIUNT\Z1725527.PATIENTRECORDS", column 'PatientID'.
The statement has been terminated.
ALTER TABLE NUTRITION ADD
CONSTRAINT PatientIDFK1FOREIGN KEY(PatientID)
REFERENCES PATIENTRECORDS(PatientID)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
This gives us a little bit of information: If we look at Line 338 or so, we see we are inserting into Nutrition (as might be guessed, given the FK error and what the FK actually IS). So, we look at the FK definition and see that that PatientID MUST be in PatientRecords for the insert to be successful. And, BECAUSE it failed earlier, due to the "datetime" messiness, we can guess this (correctly) to be the reason why.
So as hinted, fixing that datetime issue in a roundabout way corrects the other issue as well.
In general, don't get overwhelmed by a wall of errors. There is generally ONE problem at the root (or in this case, one common symptom between multiple problems).
Resolve that, redeploy and re-test. Most likely you'll end up clear of errors. OR, at least with far fewer to deal with.
Good luck 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply