MS SQL SERVER Foreign Key Question Help

[JC0133]

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)


);

 

[KB]

Can you redo the tables? I would always try to use an int field for the primary key. Its more efficient for the query compiler. I would also break out Gym and person into their own tables. Using composite keys based on strings is very inefficient.

create table Schedule
(
scheduleid int,
day date,
time time,
personid int,
gymid int,
primary key (scheduleid)
);


create table Gym
(
gymid int,
name nvarchar(100),
Address varchar(250),
state char(2),
zip nvarchar(15),
primary key (gymid)
);


create table Person
(
personid int,
name nvarchar(100),
sex char(1),
birthdate datetime,
Address varchar(250),
state char(2),
zip nvarchar(15),
primary key (personid)
);

 

[nakedfrog]

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'.

As I read it, you're trying to create an invalid foreign key reference, (name, gymName), on a table that contains the primary key (name, gymName, day, time). I concur with KB that this is not an optimal design, in any case.

 

[Cogman]

My suggestion for primary keys.

First off, primary keys should never or almost never change. A change in a primary key (or clustered index) column is really bad juju that causes a lot of data movement.

Primary keys should go from general to specific if possible. So for example, if you are constantly looking up schedules for specific gyms, your primary key should go "gymId, scheduleId" and everything that requests a schedule, if possible, should include the gym id. This has a few nice properties, especially if the data is changed often.

Next, determine the type of load the table will have. Is it read often and written infrequently? Is it written often and read infrequently? Is memory consumption a concern? Answer to those questions will determine how many or few non clustered indexes you should add to the table. A frequently written, infrequently read table, for example, should have almost no indexes on it. A frequently read infrequently written table, on the other hand, should have more indexes that match the types of queries that are common against the tables.