SQL errors when inserting data

Question

I'm trying to create a SQL Server database right now but I'm getting errors that I can't seem to solve. I'm a student trying to better myself by working on assignments I find on the internet and this one has me stumped. Below is the errors I get along with parts of my database

Msg 547, Level 16, State 0, Line 221
The INSERT statement conflicted with the FOREIGN KEY constraint "TPatients_TRandomCodes_FK". The conflict occurred in database "Final", table "dbo.TRandomCodes", column 'intRandomCodeID'.

Msg 547, Level 16, State 0, Line 294
The INSERT statement conflicted with the FOREIGN KEY constraint "TPatientVisits_TPatients_FK". The conflict occurred in database "Final", table "dbo.TPatients", column 'intPatientID'.

Here are my tables and my insert statement for the records

ALTER TABLE TSites 
    ADD CONSTRAINT TSites_TStudies_FK
        FOREIGN KEY(intStudyID) REFERENCES TStudies(intStudyID)

ALTER TABLE TPatients 
    ADD CONSTRAINT TPatients_TSites_FK
        FOREIGN KEY(intSiteID) REFERENCES TSites(intSiteID)

ALTER TABLE TVisitTypes 
    ADD CONSTRAINT TVisitTypes_TPatients_FK
        FOREIGN KEY (intPatientID) REFERENCES TPatients(intPatientID)

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

ALTER TABLE TRandomCodes 
    ADD CONSTRAINT TRandomCodes_TStudies_FK
        FOREIGN KEY(intStudyID) REFERENCES TStudies(intStudyID)

ALTER TABLE TDrugKits 
    ADD CONSTRAINT TDrugKits_TSites_FK
        FOREIGN KEY(intSiteID) REFERENCES TSites(intSiteID)

ALTER TABLE TPatientVisits 
    ADD CONSTRAINT TPatientVisits_TWithdrawReasons_FK
        FOREIGN KEY(intWithdrawReasonID) REFERENCES TWithdrawReasons(intWithdrawReasonID)

ALTER TABLE TPatients ADD CONSTRAINT TPatients_TGenders_FK
FOREIGN KEY ( intGenderID ) REFERENCES TGenders ( intGenderID )

ALTER TABLE TDrugKits ADD CONSTRAINT TDrugKits_TPatientVisits_FK
FOREIGN KEY ( intVisitID ) REFERENCES TPatientVisits ( intVisitID )

ALTER TABLE TPatients ADD CONSTRAINT TPatients_TRandomCodes_FK
FOREIGN KEY ( intRandomCodeID ) REFERENCES TRandomCodes ( intRandomCodeID )

-- Inserts for TStudies
INSERT INTO TStudies (intStudyID, strStudyDesc)
VALUES  (12345, 'Study 1'), (54321, 'Study 2')

-- Inserts for TGenders
INSERT INTO TGenders (intGenderID, strGender)
VALUES (1, 'Female'), (2, 'Male')   

-- Inserts for TSites 
INSERT INTO TSites (intSiteID, intSiteNumber, intStudyID, strName, strAddress,strCity, strState, strZip, strPhone)
VALUES (1, 101, 12345, 'Dr. Stan Heinrich', '123 E. Main St', 'Atlanta', 'GA', '25869', '1234567890'),
       (2, 111, 12345, 'Mercy Hospital', '3456 Elmhurst Rd.', 'Secaucus', 'NJ', '32659', '5013629564'),
       (3, 121, 12345, 'St. Elizabeth Hospital', '976 Jackson Way', 'Ft. Thomas', 'KY', '41258', '3026521478'),
       (4, 131, 12345, 'Dr. Jim Smith', '32454 Morris Rd.', 'Hamilton', 'OH', '45013', '3256847596'),
       (5, 141, 12345, 'Dr. Dan Jones', '1865 Jelico Hwy.', 'Knoxville', 'TN', '34568', '2145798241'),
       (6, 501, 54321, 'Dr. Robert Adler', '9087 W. Maple Ave.', 'Cedar Rapids', 'IA', '42365', '6149652574')
        ,( 7, 511,  54321, 'Dr. Tim Schmitz', '4539 Helena Run', 'Johnson City', 'TN', '34785', '5066987462' )
        ,( 8, 521,  54321, 'Dr. Lawrence Snell', '9201 NW. Washington Blvd.', 'Bristol', 'VA', '20163', '3876510249' )
        ,( 9, 531,  54321, 'Cedar Sinai Medical Center', '40321 Hollywood Blvd.', 'Portland', 'OR', '50236', '5439510246' )
        ,( 10,541,  54321, 'Vally View Hospital', '398 Hampton Rd.', 'Seattle', 'WA', '41203',  '7243780036' )

-- Inserts for TPatients
INSERT INTO TPatients ( intPatientID, intPatientNumber, intSiteID, dtmDOB, intGenderID, intWeight, intRandomCodeID  )
VALUES ( 1, 101001, 1, '01/02/1956', 1, 155, 1000 )
        ,( 2, 102001, 2, '01/02/1960', 2, 255, 1001 )
        ,( 3, 103001, 3, '01/02/1970', 1, 105, 1002 )
        ,( 4, 104001, 4, '01/02/1980', 2, 175, 1003 )   
        ,( 5, 105001, 5, '01/02/1990', 1, 115, 1004 )
        ,( 6, 106001, 6, '01/02/1993', 2, 195, 1005 )
        ,( 7, 107001, 7, '01/02/1974', 1, 125, 5000 )
        ,( 8, 108001, 8, '01/02/1969', 2, 225, 5001 )
        ,( 9, 109001, 9, '01/02/1943', 1, 113, 5002 )
        ,( 10, 110001, 10, '01/02/1984', 2, 163, 5003 )
        ,( 11, 111001, 11, '01/02/1988', 1, 100, 5004 )
        ,( 12, 112001, 12, '01/02/1977', 2, 203, 5005 ) 

-- --------------------------------------------------------------------------------
-- Inserts for TRandomCodes
-- --------------------------------------------------------------------------------
INSERT INTO TRandomCodes ( intRandomCodeID ,intRandomCode ,intStudyID, strTreatment, blnAvailable )
VALUES   ( 1,1000, 12345, 'A', 'T' )
        ,( 2,1001, 12345,   'P', 'T' )
        ,( 3,1002, 12345,   'A', 'T' )
        ,( 4, 1003, 12345,  'P', 'T' )
        ,( 5, 1004, 12345,  'P', 'T' )
        ,( 6, 1005, 12345,  'A', 'T' )
        ,( 7, 1006, 12345,  'A', 'T' )
        ,( 8, 1007, 12345,  'P', 'T' )
        ,( 9, 1008, 12345,  'A', 'T' )
        ,( 10, 1009, 12345, 'P', 'T' )
        ,( 11, 1010, 12345, 'P', 'T' )
        ,( 12, 1011, 12345, 'A', 'T' )
        ,( 13, 1012, 12345, 'P', 'T' )
        ,( 14, 1013, 12345, 'A', 'T' )
        ,( 15, 1014, 12345, 'A', 'T' )
        ,( 16, 1015, 12345, 'A', 'T' )
        ,( 17, 1016, 12345, 'P', 'T' )
        ,( 18, 1017, 12345, 'P', 'T' )
        ,( 19, 1018, 12345, 'A', 'T' )
        ,( 20, 1019, 12345, 'P', 'T' )
        ,( 21, null, null, 'NULL', 'NULL' )
        ,( 22, 5000, 54321, 'A', 'T' )
        ,( 23, 5001, 54321, 'A', 'T' )
        ,( 24, 5002, 54321, 'A', 'T' )
        ,( 25, 5003, 54321, 'A', 'T' )
        ,( 26, 5004, 54321, 'A', 'T' )
        ,( 27, 5005, 54321, 'A', 'T' )
        ,( 28, 5006, 54321, 'A', 'T' )
        ,( 29, 5007, 54321, 'A', 'T' )
        ,( 30, 5008, 54321, 'A', 'T' )
        ,( 31, 5009, 54321, 'A', 'T' )
        ,( 32, 5010, 54321, 'P', 'T' )
        ,( 33, 5011, 54321, 'P', 'T' )
        ,( 34, 5012, 54321, 'P', 'T' )
        ,( 35, 5013, 54321, 'P', 'T' )
        ,( 36, 5014, 54321, 'P', 'T' )
        ,( 37, 5015, 54321, 'P', 'T' )
        ,( 38, 5016, 54321, 'P', 'T' )
        ,( 39, 5017, 54321, 'P', 'T' )
        ,( 40, 5018, 54321, 'P', 'T' )
        ,( 41, 5019, 54321, 'P', 'T' )


-- --------------------------------------------------------------------------------
-- Inserts for TVisitTypes
-- --------------------------------------------------------------------------------
INSERT INTO TVisitTypes ( intVisitTypeID, strVisitDesc )
VALUES   ( 1, 'Screening' )
        ,( 2, 'Randomization' )
        ,( 3, 'Withdrawal' )


-- --------------------------------------------------------------------------------
-- Inserts for TPatientVisits
-- --------------------------------------------------------------------------------
INSERT INTO TPatientVisits ( intVisitID, intPatientID, dtmVisit, intVisitTypeID,intWithdrawReasonID )
VALUES   ( 1, 1, '01/01/2017', 1, 1 )
        ,( 2, 2, '02/01/2017', 2, 2 )
        ,( 3, 3, '03/01/2017', 3, 3 )
        ,( 4, 4, '04/01/2017', 1, 4 )
        ,( 5, 5, '05/01/2017', 2, 5 )
        ,( 6, 6, '06/01/2017', 3, 6 )
        ,( 7, 7, '07/01/2017', 1, 1 )
        ,( 8, 8, '08/01/2017', 2, 2 )
        ,( 9, 9, '09/01/2017', 3, 3 )
        ,( 10, 10, '10/01/2017', 1, 4 )
        ,( 11, 11, '11/01/2017', 2, 5 )
        ,( 12, 12, '12/01/2017', 3, 6 )



-- --------------------------------------------------------------------------------
-- Inserts for TDrugKits
-- --------------------------------------------------------------------------------
INSERT INTO TDrugKits ( intDrugKitID, intSiteID, strTreatment, intVisitID )
VALUES   ( 10000, 101, 'A', 'NULL' )
        ,( 10001, 101, 'A', 'NULL' )
        ,( 10002, 101, 'A', 'NULL' )
        ,( 10003, 101, 'A', 'NULL' )
        ,( 10004, 101, 'P', 'NULL' )
        ,( 10005, 101, 'P', 'NULL' )
        ,( 10006, 101, 'P', 'NULL' )
        ,( 10007, 101, 'P', 'NULL' )
        ,( 10008, 111, 'A', 'NULL' )
        ,( 10009, 111, 'A', 'NULL' )
        ,( 10010, 111, 'A', 'NULL' )
        ,( 10011, 111, 'A', 'NULL' )
        ,( 10012, 111, 'P', 'NULL' )
        ,( 10013, 111, 'P', 'NULL' )
        ,( 10014, 111, 'P', 'NULL' )
        ,( 10015, 111, 'P', 'NULL' )
        ,( 10016, 121, 'A', 'NULL' )
        ,( 10017, 121, 'A', 'NULL' )
        ,( 10018, 121, 'A', 'NULL' )
        ,( 10019, 121, 'A', 'NULL' )
        ,( 10020, 121, 'P', 'NULL' )
        ,( 10021, 121, 'P', 'NULL' )
        ,( 10022, 121, 'P', 'NULL' )
        ,( 10023, 121, 'P', 'NULL' )
        ,( 10024, 131, 'A', 'NULL' )
        ,( 10025, 131, 'A', 'NULL' )
        ,( 10026, 131, 'A', 'NULL' )
        ,( 10027, 131, 'A', 'NULL' )
        ,( 10028, 131, 'P', 'NULL' )
        ,( 10029, 131, 'P', 'NULL' )
        ,( 10030, 131, 'P', 'NULL' )
        ,( 10031, 131, 'P', 'NULL' )
        ,( 10032, 141, 'A', 'NULL' )
        ,( 10033, 141, 'A', 'NULL' )
        ,( 10034, 141, 'A', 'NULL' )
        ,( 10035, 141, 'A', 'NULL' )
        ,( 10036, 141, 'P', 'NULL' )
        ,( 10037, 141, 'P', 'NULL' )
        ,( 10038, 141, 'P', 'NULL' )
        ,( 10039, 141, 'P', 'NULL' )
        ,( 10040, 501, 'A', 'NULL' )
        ,( 10041, 501, 'A', 'NULL' )
        ,( 10042, 501, 'A', 'NULL' )
        ,( 10043, 501, 'A', 'NULL' )
        ,( 10044, 501, 'P', 'NULL' )
        ,( 10045, 501, 'P', 'NULL' )
        ,( 10046, 501, 'P', 'NULL' )
        ,( 10047, 501, 'P', 'NULL' )
        ,( 10048, 511, 'A', 'NULL' )
        ,( 10049, 511, 'A', 'NULL' )
        ,( 10050, 511, 'A', 'NULL' )
        ,( 10051, 511, 'A', 'NULL' )
        ,( 10052, 511, 'P', 'NULL' )
        ,( 10053, 511, 'P', 'NULL' )
        ,( 10054, 511, 'P', 'NULL' )
        ,( 10055, 511, 'P', 'NULL' )
        ,( 10056, 521, 'A', 'NULL' )
        ,( 10057, 521, 'A', 'NULL' )
        ,( 10058, 521, 'A', 'NULL' )
        ,( 10059, 521, 'A', 'NULL' )
        ,( 10060, 521, 'P', 'NULL' )
        ,( 10061, 521, 'P', 'NULL' )
        ,( 10062, 521, 'P', 'NULL' )
        ,( 10063, 521, 'P', 'NULL' )
        ,( 10064, 531, 'A', 'NULL' )
        ,( 10065, 531, 'A', 'NULL' )
        ,( 10066, 531, 'A', 'NULL' )
        ,( 10067, 531, 'A', 'NULL' )
        ,( 10068, 531, 'P', 'NULL' )
        ,( 10069, 531, 'P', 'NULL' )
        ,( 10070, 531, 'P', 'NULL' )
        ,( 10071, 531, 'P', 'NULL' )
        ,( 10072, 541, 'A', 'NULL' )
        ,( 10073, 541, 'A', 'NULL' )
        ,( 10074, 541, 'A', 'NULL' )
        ,( 10075, 541, 'A', 'NULL' )
        ,( 10076, 541, 'P', 'NULL' )
        ,( 10077, 541, 'P', 'NULL' )
        ,( 10078, 541, 'P', 'NULL' )
        ,( 10079, 541, 'P', 'NULL' )

-- --------------------------------------------------------------------------------
-- Inserts for TWithdrawReasons
-- --------------------------------------------------------------------------------
INSERT INTO TWithdrawReasons ( intWithdrawReasonID, strWithdrawDesc )
VALUES   (1, 'Patient withdrew consent' )
        ,(2, 'Adverse event' )
        ,(3, 'Health issue-related to study' )
        ,(4, 'Health issue-unrelated to study' )
        ,(5, 'Personal reason' )
        ,(6, 'Completed the study' )

score 1 · Answer 1 · answered Apr 03 '18 at 18:13

Double-click on the error message, watch the cursor jump to the line that threw the error, and make note of the statement.
Find the foreign keys (TPatients_TRandomCodes_FK and TPatientVisits_TPatients_FK), and eyeball the tables involved.
You'll find that your INSERT statements are trying to insert rows into one table, but the FK requires there be a related row in that table before the INSERT can succeed. So you'll have to INSERT those rows first, then the ones that threw the error.

For example, most companies do not allow an order to be entered where the CustomerID does not exist in the Customer table. Most schools do not allow a student-class assignemnt to occur where the respective ID's are not in both the Student and Class table.

score 0 · Answer 2 · answered Apr 03 '18 at 18:15

0

If you run these queries in this order you're first putting data into TPatients and then into TRandomCodes. But in the first section where you create the foreign key constraints, you add a foreign key constraint for the column intRandomCodeID in the TPatients table to reference intRandomCodeID in the TRandomCodes table. When you put data in the TPatients table, the server checks if the data for the intRandomCodeID column exists in the TRandomCodes table, due to the constraint that was put in place. When the server sees that no such row (data) exist, it throws an error.

answered Apr 03 '18 at 18:15

Timovski

571
2
6

I moved the TRandomCode insert above the TPatients and still get the same error I think this i s what you're telling me to do but I'm not to sure – NoLuckCollector Apr 03 '18 at 19:01
Another problem is that in your TRandomCodes table, you're inserting the values 1, 2, 3... for intRandomCodeID. But, in your TPatients table, the column values for intRandomCodeID are 1000, 1001, 1002... Like before, the server tries to find those values in the TRandomCodes table, it fails, and then throws an error. – Timovski Apr 03 '18 at 19:10

SQL errors when inserting data

2 Answers2