Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Relationships -_- Completely need help Expand / Collapse
Author
Message
Posted Thursday, April 24, 2014 5:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:24 AM
Points: 61, Visits: 69
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)...
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)
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 :)
Post #1564924
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse