MS SQL SERVER Foreign Key Question Help

[JC724]

If someone can help me with this script I would greatly appreciate it.

So I am basically trying to use a Foreign Key to reference a table with multiple Primary Keys and I keep getting and Error. When I run the create table script for personal trainer I get this error.

Error is

Msg 1776, Level 16, State 0, Line 3
There are no primary or candidate keys in the referenced table 'Schedule' that match the referencing column list in the foreign key 'FK__Personal_Trainer__38996AB5'.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint or index. See previous errors.


Here is the create table script for both tables. I am trying to use a foreign key in personal trainer to reference the table in schedule. FitnessWebApp is the name of the database.

use FitnessWebApp

create table Schedule
(
day char(20),
time char(20),
name char(30),
gymName char(30)
primary key (name, gymName, day, time)

);

use FitnessWebApp

create table Personal_Trainer
(
name char(30),
gymName char(30)
primary key(name, gymName),
foreign key (name, gymName) REFERENCES Schedule(name, gymName)


);

 

[modi123]

If it was me I would rewrite the majority of that. Have your primary keys be more simplistic - an incrementing integer, a GUID, etc. Definitely not a varchar like 'name'. I would also have a third table called 'gym' where it keeps an ID key and a varchar name. The other tables would reference that ID number and not a name.

To be more specific:

cr eate table #Schedule
(
ID int IDENTITY(1,1) PRIMARY KEY,
slot DATETIME,
TRAINER_ID int,
GYM_ID int,
DATE_ENTERED datetime
);
AL TER TABLE #Schedule ADD CONSTRAINT DF_#Schedule DEFAULT GETDATE() FOR DATE_ENTERED

cr eate table #Gym
(
ID int IDENTITY(1,1) PRIMARY KEY,
name char(30),
DATE_ENTERED datetime
);
AL TER TABLE #Gym ADD CONSTRAINT DF_#Gym DEFAULT GETDATE() FOR DATE_ENTERED

cr eate table #Personal_Trainer
(
ID int IDENTITY(1,1) PRIMARY KEY,
name varchar(30),
DATE_ENTERED datetime
);
AL TER TABLE #Personal_Trainer ADD CONSTRAINT DF_#Personal_Trainer DEFAULT GETDATE() FOR DATE_ENTERED

Then you can insert values pretty simply:

IN SERT INTO #Personal_Trainer(name) VALUES ('foo')
IN SERT INTO #Personal_Trainer(name) VALUES ('bar')

IN SERT INTO #Gym (name) VALUES ('gym1')
IN SERT INTO #Gym (name) VALUES ('gym2')

IN SERT INTO #Schedule (slot, TRAINER_ID, GYM_ID) VALUES ('01/01/2017 12:00', 1, 1)
IN SERT INTO #Schedule (slot, TRAINER_ID, GYM_ID) VALUES ('01/02/2017 12:00', 1, 1)
IN SERT INTO #Schedule (slot, TRAINER_ID, GYM_ID) VALUES ('01/03/2017 12:00', 1, 1)
IN SERT INTO #Schedule (slot, TRAINER_ID, GYM_ID) VALUES ('01/01/2017 12:00', 2, 2)

select * from #Personal_Trainer
select * from #Gym
select * from #Schedule

ID          name                           DATE_ENTERED
----------- ------------------------------ -----------------------
1           foo                            2017-03-07 08:56:25.663
2           bar                            2017-03-07 08:56:25.663

(2 row(s) affected)

ID          name                           DATE_ENTERED
----------- ------------------------------ -----------------------
1           gym1                           2017-03-07 08:56:25.663
2           gym2                           2017-03-07 08:56:25.663

(2 row(s) affected)

ID          slot                    TRAINER_ID  GYM_ID      DATE_ENTERED
----------- ----------------------- ----------- ----------- -----------------------
1           2017-01-01 12:00:00.000 1           1           2017-03-07 08:56:25.667
2           2017-01-02 12:00:00.000 1           1           2017-03-07 08:56:25.667
3           2017-01-03 12:00:00.000 1           1           2017-03-07 08:56:25.667
4           2017-01-01 12:00:00.000 2           2           2017-03-07 08:56:25.667

SELECT b.name as gymName, c.name as trainerName, a.slot
from #Schedule a
JOIN #Gym b on a.GYM_ID = b.ID
join #Personal_Trainer c on a.TRAINER_ID = c.ID

gymName                        trainerName                    slot
------------------------------ ------------------------------ -----------------------
gym1                           foo                            2017-01-01 12:00:00.000
gym1                           foo                            2017-01-02 12:00:00.000
gym1                           foo                            2017-01-03 12:00:00.000
gym2                           bar                            2017-01-01 12:00:00.000

dr op table #Personal_Trainer
dr op table #Gym
dr op table #Schedule