trouble sussing out foreign keys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

trouble sussing out foreign keys

Author

Topic

suzannecoop
Starting Member

4 Posts

Posted - 2014-09-11 : 02:34:21

Im creating a database where a table column may have mulitple values for the one column to create my table i used

CREATE TABLE Medics (
MedicNo Varchar(10) PRIMARY KEY,
MedicFName nvarchar(30),
MedicLName nvarchar(30)
);

CREATE TABLE Paitent (
PaitentNo Varchar(10) PRIMARY KEY,
PaitentFName nvarchar(30),
PaitentlName nvarchar(30)
);

Create TABLE Medicine (
MedicineNo Varchar(10),
MedicineName Varchar(10) PRIMARY KEY,
MedicineUnit varchar(10),
MedicinePricePU varchar(10)
);

CREATE TABLE Prescription (
PrescriptionNo varchar(10),
PrescriptionDate Datetime,
MedicNo varchar(10)FOREIGN KEY REFERENCES Medics,
PaitentNo varchar(10)FOREIGN KEY REFERENCES Paitent,
Herb1 varchar(10)FOREIGN KEY (MedicineName)REFERENCES Medicine(MedicineName),
Herb2 varchar(10)FOREIGN KEY (MedicineName)REFERENCES Medicine(MedicineName),
Herb3 varchar(10)FOREIGN KEY (MedicineName)REFERENCES Medicine(MedicineName),
Herb4 varchar(10)FOREIGN KEY (MedicineName)REFERENCES Medicine(MedicineName),
Herb5 varchar(10)FOREIGN KEY (MedicineName)REFERENCES Medicine(MedicineName),
);

and attempted to insert

INSERT INTO Medics VALUES ('1' ,'Sharon', 'Cooper')
INSERT INTO Medics VALUES ('2','Martin', 'Spuds')
INSERT INTO Medics VALUES ('3','Suzie','staples')
INSERT INTO Medics VALUES ('4','Evan', 'butts')
INSERT INTO Medics VALUES ('5', 'Katie', 'Crentist')

INSERT INTO Paitent VALUES ('89' ,'Fred', 'Hollows')
INSERT INTO Paitent VALUES ('88','Peter', 'Gaunt')
INSERT INTO Paitent VALUES ('87','Gary','Glitter')
INSERT INTO Paitent VALUES ('86','Freddie', 'May')
INSERT INTO Paitent VALUES ('85', 'Brian', 'Palmer')

INSERT INTO Prescription VALUES ('1' ,'12 may08', '1',’88’,’Sage’,’Parsely’,’Rosemary’,’NULL’,’NULL’)
INSERT INTO Prescription VALUES ('2','2 jun08','1', ‘85’, ’Ginger’,’Sage’,’Oregano’,’NULL’,’NULL’)
INSERT INTO Prescription VALUES ('3','6 Jun08','2',’86’, ’Basil’,’Oregano’,’Sage’,’Ginger’,’Thyme’)
INSERT INTO Prescription VALUES ('4','7 Jun08', '4',’87’,’Rosemary’,’Thyme’,’Sage’,’NULL’,’NULL’)
INSERT INTO Prescription VALUES ('5','7 Jun08','5',’88’, ’Oregano’,’Parsley’,’Thyme’,’Sage’,’Basil’)

INSERT INTO Medicine VALUES ('1','Sage','300','$0.02' )
INSERT INTO Medicine VALUES ('2','Parsely','500','$0.02')
INSERT INTO Medicine VALUES ('3','Rosemary','500','$0.02')
INSERT INTO Medicine VALUES ('4','Thyme','300','$0.02')
INSERT INTO Medicine VALUES ('5','Basil','600','$0.02')
INSERT INTO Medicine VALUES ('6','Oregon','350','$0.02')
INSERT INTO Medicine VALUES ('7','Ginger','375','$0.02')

what have i done wrong ? im getting the error
Msg 1769, Level 16, State 1, Line 21
Foreign key 'Herb1' references invalid column 'MedicineName' in referencing table 'Prescription'.
Msg 1750, Level 16, State 0, Line 21
Could not create constraint. See previous errors.

any help or pointer would be very very much appreciated

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-09-11 : 05:05:52

you don't have MedicineName column in Prescription table.
what I can guess from your script is that, you want to create a FKey on herb1,2,3,4,5 columns referencing to medicinename column in medicine table.
if that's the case, this should work.

CREATE TABLE Prescription (
PrescriptionNo varchar(10),
PrescriptionDate Datetime,
MedicNo varchar(10)FOREIGN KEY REFERENCES Medics,
PaitentNo varchar(10)FOREIGN KEY REFERENCES Paitent,
Herb1 varchar(10)FOREIGN KEY (Herb1)REFERENCES Medicine(MedicineName),
Herb2 varchar(10)FOREIGN KEY (Herb2)REFERENCES Medicine(MedicineName),
Herb3 varchar(10)FOREIGN KEY (Herb3)REFERENCES Medicine(MedicineName),
Herb4 varchar(10)FOREIGN KEY (Herb4)REFERENCES Medicine(MedicineName),
Herb5 varchar(10)FOREIGN KEY (Herb5)REFERENCES Medicine(MedicineName),
);

Javeed Ahmed

suzannecoop
Starting Member

4 Posts

Posted - 2014-09-14 : 22:40:31

thanks heaps Javeed Ahmed much appreciated that you fixed my tables
however i was having just one more problem with my insert into the perscription table ive made all the no. unique for the primary keys

INSERT INTO Medics VALUES ('51' ,'Sharon', 'Cooper')
INSERT INTO Medics VALUES ('52','Martin', 'Spuds')
INSERT INTO Medics VALUES ('53','Suzie','staples')
INSERT INTO Medics VALUES ('54','Evan', 'butts')
INSERT INTO Medics VALUES ('55', 'Katie', 'Crentist')

INSERT INTO Paitent VALUES ('89' ,'Fred', 'Hollows')
INSERT INTO Paitent VALUES ('88','Peter', 'Gaunt')
INSERT INTO Paitent VALUES ('87','Gary','Glitter')
INSERT INTO Paitent VALUES ('86','Freddie', 'May')
INSERT INTO Paitent VALUES ('85', 'Brian', 'Palmer')

INSERT INTO Prescription VALUES ('101' ,'12 may08', '1','88','Sage','Parsely','Rosemary','NULL','NULL')
INSERT INTO Prescription VALUES ('102','2 jun08','1', '85', 'Ginger','Sage','Oregano','NULL','NULL')
INSERT INTO Prescription VALUES ('103','6 Jun08','2','86', 'Basil','Oregano','Sage','Ginger','Thyme')
INSERT INTO Prescription VALUES ('104','7 Jun08', '4','87','Rosemary','Thyme','Sage','NULL','NULL')
INSERT INTO Prescription VALUES ('105','7 Jun08','5','88','Oregano','Parsley','Thyme','Sage','Basil')

INSERT INTO Medicine VALUES ('1','Sage','300','$0.05' )
INSERT INTO Medicine VALUES ('2','Parsely','500','$0.09')
INSERT INTO Medicine VALUES ('3','Rosemary','500','$0.02')
INSERT INTO Medicine VALUES ('4','Thyme','300','$0.09')
INSERT INTO Medicine VALUES ('5','Basil','600','$0.14')
INSERT INTO Medicine VALUES ('6','Oregon','350','$0.32')
INSERT INTO Medicine VALUES ('7','Ginger','375','$0.70')
but im getting this error
Msg 547, Level 16, State 0, Line 15
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Prescript__Herb1__2C1E8537". The conflict occurred in database "scooper1", table "scooper1.Medicine", column 'MedicineName'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 16
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Prescript__Herb1__2C1E8537". The conflict occurred in database "scooper1", table "scooper1.Medicine", column 'MedicineName'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 17
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Prescript__Herb1__2C1E8537". The conflict occurred in database "scooper1", table "scooper1.Medicine", column 'MedicineName'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 18
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Prescript__Herb1__2C1E8537". The conflict occurred in database "scooper1", table "scooper1.Medicine", column 'MedicineName'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 19
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Prescript__Herb1__2C1E8537". The conflict occurred in database "scooper1", table "scooper1.Medicine", column 'MedicineName'.
The statement has been terminated.

would you possibly know what ive done wrong?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-09-15 : 01:02:18

there were some typos and also incorrect reference values for MedicNo. Below are the corrected INSERT statements.

NOTE:
1. The parent table should have the values which are referenced into the child table. So you must insert values to PARENT TABLE first and then use those referenced values to child table.
2. If you keep sinle quotation mark to the NULL, it will be considered as a string value but not the UNKNOWN value.

quote:
Originally posted by suzannecoop

thanks heaps Javeed Ahmed much appreciated that you fixed my tables
however i was having just one more problem with my insert into the perscription table ive made all the no. unique for the primary keys

INSERT INTO Medics VALUES ('51' ,'Sharon', 'Cooper')
INSERT INTO Medics VALUES ('52','Martin', 'Spuds')
INSERT INTO Medics VALUES ('53','Suzie','staples')
INSERT INTO Medics VALUES ('54','Evan', 'butts')
INSERT INTO Medics VALUES ('55', 'Katie', 'Crentist')

INSERT INTO Paitent VALUES ('89' ,'Fred', 'Hollows')
INSERT INTO Paitent VALUES ('88','Peter', 'Gaunt')
INSERT INTO Paitent VALUES ('87','Gary','Glitter')
INSERT INTO Paitent VALUES ('86','Freddie', 'May')
INSERT INTO Paitent VALUES ('85', 'Brian', 'Palmer')

INSERT INTO Medicine VALUES ('1','Sage','300','$0.05' )
INSERT INTO Medicine VALUES ('2','Parsely','500','$0.09')
INSERT INTO Medicine VALUES ('3','Rosemary','500','$0.02')
INSERT INTO Medicine VALUES ('4','Thyme','300','$0.09')
INSERT INTO Medicine VALUES ('5','Basil','600','$0.14')
INSERT INTO Medicine VALUES ('6','Oregon','350','$0.32')
INSERT INTO Medicine VALUES ('7','Ginger','375','$0.70')

INSERT INTO Prescription VALUES ('101' ,'12 may08', '51','88','Sage','Parsely','Rosemary',NULL,NULL)
INSERT INTO Prescription VALUES ('102','2 jun08','51', '85', 'Ginger','Sage','Oregon',NULL,NULL)
INSERT INTO Prescription VALUES ('103','6 Jun08','52','86', 'Basil','Oregon','Sage','Ginger','Thyme')
INSERT INTO Prescription VALUES ('104','7 Jun08', '54','87','Rosemary','Thyme','Sage',NULL,NULL)
INSERT INTO Prescription VALUES ('105','7 Jun08','55','88','Oregon','Parsely','Thyme','Sage','Basil')

would you possibly know what ive done wrong?

--
Chandu

suzannecoop
Starting Member

4 Posts

Posted - 2014-09-15 : 21:01:52

CREATE TABLE Medics (
MedicNo Varchar(10) PRIMARY KEY,
MedicFName nvarchar(30),
MedicLName nvarchar(30)
);

CREATE TABLE Paitent (
PaitentNo Varchar(10) PRIMARY KEY,
PaitentFName nvarchar(30),
PaitentlName nvarchar(30)
);

Create TABLE Medicine (
MedicineNo Varchar(10) PRIMARY KEY,
MedicineName Varchar(10),
MedicineUnit Numeric(10),
MedicinePricePU Numeric(10)
);

CREATE TABLE Prescription (
PrescriptionNo varchar(10),
PrescriptionDate Datetime,
MedicNo varchar(10)FOREIGN KEY (MedicNo)REFERENCES Medics(MedicNo),
PaitentNo varchar(10)FOREIGN KEY (PaitentNo)REFERENCES Paitent(PaitentNo),
HerbNo1 Varchar (10) FOREIGN KEY (HerbNo1) REFERENCES Medicine(MedicineNo),
HerbNo2 Varchar (10) FOREIGN KEY (HerbNo2) REFERENCES Medicine(MedicineNo),
HerbNo3 Varchar (10) FOREIGN KEY (HerbNo3) REFERENCES Medicine(MedicineNo),
HerbNo4 Varchar (10) FOREIGN KEY (HerbNo4) REFERENCES Medicine(MedicineNo),
HerbNo5 Varchar (10) FOREIGN KEY (HerbNo5) REFERENCES Medicine(MedicineNo),

);

INSERT INTO Medics VALUES ('51','Sharon','Cooper')
INSERT INTO Medics VALUES ('52','Martin','Spuds')
INSERT INTO Medics VALUES ('53','Suzie','staples')
INSERT INTO Medics VALUES ('54','Evan','butts')
INSERT INTO Medics VALUES ('55','Katie','Crentist')

INSERT INTO Paitent VALUES ('89','Fred','Hollows')
INSERT INTO Paitent VALUES ('88','Peter','Gaunt')
INSERT INTO Paitent VALUES ('87','Gary','Glitter')
INSERT INTO Paitent VALUES ('86','Freddie','May')
INSERT INTO Paitent VALUES ('85','Brian','Palmer')

INSERT INTO Prescription VALUES ('101' ,'12 may08','51','88','1','2','3',NULL,NULL)
INSERT INTO Prescription VALUES ('102','2 jun08','51', '85','7','1','6',NULL,NULL)
INSERT INTO Prescription VALUES ('103','6 Jun08','52','86', '5','6','1','7','4')
INSERT INTO Prescription VALUES ('104','7 Jun08','54','87','3','4','1',NULL,NULL)
INSERT INTO Prescription VALUES ('105','7 Jun08','55','88','6','2','4','1','5')

INSERT INTO Medicine VALUES ('1','Sage','300','$0.05' )
INSERT INTO Medicine VALUES ('2','Parsely','500','$0.09')
INSERT INTO Medicine VALUES ('3','Rosemary','500','$0.02')
INSERT INTO Medicine VALUES ('4','Thyme','300','$0.09')
INSERT INTO Medicine VALUES ('5','Basil','600','$0.14')
INSERT INTO Medicine VALUES ('6','Oregon','350','$0.32')
INSERT INTO Medicine VALUES ('7','Ginger','375','$0.70')

after having to change my database design i tried to stay true to your help however I need your help again :/
sg 547, Level 16, State 0, Line 14
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Prescript__HerbN__511AFFBC". The conflict occurred in database "scooper1", table "scooper1.Medicine", column 'MedicineNo'.
The statement has been terminated. im still getting this error
THANK YOU ALL SOOOOO MUCH only place to go for help :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources