Group: General Forum Members
Last Login: Friday, May 15, 2015 10:34 AM
|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 (
Zipcode Numeric(5) NOT NULL,
HireDate Datetime NOT NULL,
LocID Int Not Null,
CONSTRAINT EmployeePK PRIMARY 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 PatientIDFK1 FOREIGN KEY(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 :)