Creating a Foreign Key between two tables in SQL
I'm trying to join two tables together using a foreign key. I am getting the error
There are no primary keys in the referenced table that match the referencing column list
I'm unsure of what I'm missing.
if exists(select * from sysobjects where name = 'Riders')
drop table Riders
go
create table Riders
(
RiderID int not null identity (10,1)
constraint pk_Riders_RiderID primary key(RiderID,ClassID),
[Name] nvarchar(50) not null,
constraint chk_Riders_Name check (len(Name) > 4),
ClassID nchar(6) not null
)
go
if exists( select * from sysobjects where name = 'Class')
drop table Class
go
create table Class
(
ClassDescription nvarchar(50) not null,
ClassID nchar(6) not null
constraint fk_Riders_Class foreign key
references Riders(ClassID) on delete no action
)
go
sql
sql-server foreign-keys edited Nov 25 '18 at 19:06
marc_s
579k12911181265
asked Nov 25 '18 at 17:59
V.MV.M
145
add a comment |
I'm trying to join two tables together using a foreign key. I am getting the error
There are no primary keys in the referenced table that match the referencing column list
I'm unsure of what I'm missing.
if exists(select * from sysobjects where name = 'Riders')
drop table Riders
go
create table Riders
(
RiderID int not null identity (10,1)
constraint pk_Riders_RiderID primary key(RiderID,ClassID),
[Name] nvarchar(50) not null,
constraint chk_Riders_Name check (len(Name) > 4),
ClassID nchar(6) not null
)
go
if exists( select * from sysobjects where name = 'Class')
drop table Class
go
create table Class
(
ClassDescription nvarchar(50) not null,
ClassID nchar(6) not null
constraint fk_Riders_Class foreign key
references Riders(ClassID) on delete no action
)
go
sql
sql-server foreign-keys edited Nov 25 '18 at 19:06
marc_s
579k12911181265
asked Nov 25 '18 at 17:59
V.MV.M
145
WHy have you declared Riders to have a composite primary key?
– Caius Jard
Nov 25 '18 at 18:10
It did not recognize ClassID as a key when I tried to join it to Class. So I made both of them primary keys.
– V.M
Nov 25 '18 at 18:11
The FK must reference a complete key, not just one of its columns if it has two columns.
– jarlh
Nov 25 '18 at 18:13
What is ClassID? Is there a class table as well?
– jarlh
Nov 25 '18 at 18:14
add a comment |
I'm trying to join two tables together using a foreign key. I am getting the error
There are no primary keys in the referenced table that match the referencing column list
I'm unsure of what I'm missing.
if exists(select * from sysobjects where name = 'Riders')
drop table Riders
go
create table Riders
(
RiderID int not null identity (10,1)
constraint pk_Riders_RiderID primary key(RiderID,ClassID),
[Name] nvarchar(50) not null,
constraint chk_Riders_Name check (len(Name) > 4),
ClassID nchar(6) not null
)
go
if exists( select * from sysobjects where name = 'Class')
drop table Class
go
create table Class
(
ClassDescription nvarchar(50) not null,
ClassID nchar(6) not null
constraint fk_Riders_Class foreign key
references Riders(ClassID) on delete no action
)
go
sql
sql-server foreign-keys edited Nov 25 '18 at 19:06
marc_s
579k12911181265
asked Nov 25 '18 at 17:59
V.MV.M
145
I'm trying to join two tables together using a foreign key. I am getting the error
There are no primary keys in the referenced table that match the referencing column list
I'm unsure of what I'm missing.
if exists(select * from sysobjects where name = 'Riders')
drop table Riders
go
create table Riders
(
RiderID int not null identity (10,1)
constraint pk_Riders_RiderID primary key(RiderID,ClassID),
[Name] nvarchar(50) not null,
constraint chk_Riders_Name check (len(Name) > 4),
ClassID nchar(6) not null
)
go
if exists( select * from sysobjects where name = 'Class')
drop table Class
go
create table Class
(
ClassDescription nvarchar(50) not null,
ClassID nchar(6) not null
constraint fk_Riders_Class foreign key
references Riders(ClassID) on delete no action
)
go
sql
sql-server foreign-keys sql
sql-server foreign-keys edited Nov 25 '18 at 19:06
marc_s
579k12911181265
asked Nov 25 '18 at 17:59
V.MV.M
145
edited Nov 25 '18 at 19:06
marc_s
579k12911181265
asked Nov 25 '18 at 17:59
V.MV.M
145
edited Nov 25 '18 at 19:06
marc_s
579k12911181265
edited Nov 25 '18 at 19:06
marc_s
579k12911181265
edited Nov 25 '18 at 19:06
marc_s
579k12911181265
579k12911181265
asked Nov 25 '18 at 17:59
V.MV.M
145
asked Nov 25 '18 at 17:59
V.MV.M
145
asked Nov 25 '18 at 17:59
V.MV.M
145
145
WHy have you declared Riders to have a composite primary key?
– Caius Jard
Nov 25 '18 at 18:10
It did not recognize ClassID as a key when I tried to join it to Class. So I made both of them primary keys.
– V.M
Nov 25 '18 at 18:11
The FK must reference a complete key, not just one of its columns if it has two columns.
– jarlh
Nov 25 '18 at 18:13
What is ClassID? Is there a class table as well?
– jarlh
Nov 25 '18 at 18:14
add a comment |
WHy have you declared Riders to have a composite primary key?
– Caius Jard
Nov 25 '18 at 18:10
It did not recognize ClassID as a key when I tried to join it to Class. So I made both of them primary keys.
– V.M
Nov 25 '18 at 18:11
The FK must reference a complete key, not just one of its columns if it has two columns.
– jarlh
Nov 25 '18 at 18:13
What is ClassID? Is there a class table as well?
– jarlh
Nov 25 '18 at 18:14
WHy have you declared Riders to have a composite primary key?
– Caius Jard
Nov 25 '18 at 18:10
WHy have you declared Riders to have a composite primary key?
– Caius Jard
Nov 25 '18 at 18:10
It did not recognize ClassID as a key when I tried to join it to Class. So I made both of them primary keys.
– V.M
Nov 25 '18 at 18:11
It did not recognize ClassID as a key when I tried to join it to Class. So I made both of them primary keys.
– V.M
Nov 25 '18 at 18:11
The FK must reference a complete key, not just one of its columns if it has two columns.
– jarlh
Nov 25 '18 at 18:13
The FK must reference a complete key, not just one of its columns if it has two columns.
– jarlh
Nov 25 '18 at 18:13
What is ClassID? Is there a class table as well?
– jarlh
Nov 25 '18 at 18:14
What is ClassID? Is there a class table as well?
– jarlh
Nov 25 '18 at 18:14
add a comment |
1 Answer
1
active
oldest
votes
You seem to have your relationship the wrong way round
Class would have a primary key of ClassID; there must be no row in class that has a duplicate primary key. Class (as a table) hence decodes your class code into a class name (SNR=Senior Sport Series 1, 50CC=Fifty CC 2 Stroke Cup etc)
Riders requires a foreign key in that the Riders.ClassID column references Class.ClassID - the Classid column in Riders will have duplicated values (multiple riders are all in the same class) but the relationship you're wishing to enforce is that "no rider shall be entered into an unknown class" i.e. "no rider record shall have a classid value that is not present in the classid column of the class table"
You're hence looking for something more like:
if exists( select * from sysobjects where name = 'Class')
drop table Class
go
create table Class
(
ClassDescription nvarchar(50) not null,
ClassID nchar(6) not null constraint pk_Class_ClassID primary key(ClassID)
)
go
create table Riders
(
RiderID int not null identity (10,1)
constraint pk_Riders_RiderID primary key(RiderID),
[Name] nvarchar(50) not null,
constraint chk_Riders_Name check (len(Name) > 4),
ClassID nchar(6) not null
constraint fk_Riders_Class foreign key
references Class(ClassID) on delete no action
)
go
answered Nov 25 '18 at 18:15
Caius JardCaius Jard
12k21240
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53470307%2fcreating-a-foreign-key-between-two-tables-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You seem to have your relationship the wrong way round
Class would have a primary key of ClassID; there must be no row in class that has a duplicate primary key. Class (as a table) hence decodes your class code into a class name (SNR=Senior Sport Series 1, 50CC=Fifty CC 2 Stroke Cup etc)
Riders requires a foreign key in that the Riders.ClassID column references Class.ClassID - the Classid column in Riders will have duplicated values (multiple riders are all in the same class) but the relationship you're wishing to enforce is that "no rider shall be entered into an unknown class" i.e. "no rider record shall have a classid value that is not present in the classid column of the class table"
You're hence looking for something more like:
if exists( select * from sysobjects where name = 'Class')
drop table Class
go
create table Class
(
ClassDescription nvarchar(50) not null,
ClassID nchar(6) not null constraint pk_Class_ClassID primary key(ClassID)
)
go
create table Riders
(
RiderID int not null identity (10,1)
constraint pk_Riders_RiderID primary key(RiderID),
[Name] nvarchar(50) not null,
constraint chk_Riders_Name check (len(Name) > 4),
ClassID nchar(6) not null
constraint fk_Riders_Class foreign key
references Class(ClassID) on delete no action
)
go
answered Nov 25 '18 at 18:15
Caius JardCaius Jard
12k21240
add a comment |
You seem to have your relationship the wrong way round
Class would have a primary key of ClassID; there must be no row in class that has a duplicate primary key. Class (as a table) hence decodes your class code into a class name (SNR=Senior Sport Series 1, 50CC=Fifty CC 2 Stroke Cup etc)
Riders requires a foreign key in that the Riders.ClassID column references Class.ClassID - the Classid column in Riders will have duplicated values (multiple riders are all in the same class) but the relationship you're wishing to enforce is that "no rider shall be entered into an unknown class" i.e. "no rider record shall have a classid value that is not present in the classid column of the class table"
You're hence looking for something more like:
if exists( select * from sysobjects where name = 'Class')
drop table Class
go
create table Class
(
ClassDescription nvarchar(50) not null,
ClassID nchar(6) not null constraint pk_Class_ClassID primary key(ClassID)
)
go
create table Riders
(
RiderID int not null identity (10,1)
constraint pk_Riders_RiderID primary key(RiderID),
[Name] nvarchar(50) not null,
constraint chk_Riders_Name check (len(Name) > 4),
ClassID nchar(6) not null
constraint fk_Riders_Class foreign key
references Class(ClassID) on delete no action
)
go
answered Nov 25 '18 at 18:15
Caius JardCaius Jard
12k21240
add a comment |
You seem to have your relationship the wrong way round
Class would have a primary key of ClassID; there must be no row in class that has a duplicate primary key. Class (as a table) hence decodes your class code into a class name (SNR=Senior Sport Series 1, 50CC=Fifty CC 2 Stroke Cup etc)
Riders requires a foreign key in that the Riders.ClassID column references Class.ClassID - the Classid column in Riders will have duplicated values (multiple riders are all in the same class) but the relationship you're wishing to enforce is that "no rider shall be entered into an unknown class" i.e. "no rider record shall have a classid value that is not present in the classid column of the class table"
You're hence looking for something more like:
if exists( select * from sysobjects where name = 'Class')
drop table Class
go
create table Class
(
ClassDescription nvarchar(50) not null,
ClassID nchar(6) not null constraint pk_Class_ClassID primary key(ClassID)
)
go
create table Riders
(
RiderID int not null identity (10,1)
constraint pk_Riders_RiderID primary key(RiderID),
[Name] nvarchar(50) not null,
constraint chk_Riders_Name check (len(Name) > 4),
ClassID nchar(6) not null
constraint fk_Riders_Class foreign key
references Class(ClassID) on delete no action
)
go
answered Nov 25 '18 at 18:15
Caius JardCaius Jard
12k21240
You seem to have your relationship the wrong way round
Class would have a primary key of ClassID; there must be no row in class that has a duplicate primary key. Class (as a table) hence decodes your class code into a class name (SNR=Senior Sport Series 1, 50CC=Fifty CC 2 Stroke Cup etc)
Riders requires a foreign key in that the Riders.ClassID column references Class.ClassID - the Classid column in Riders will have duplicated values (multiple riders are all in the same class) but the relationship you're wishing to enforce is that "no rider shall be entered into an unknown class" i.e. "no rider record shall have a classid value that is not present in the classid column of the class table"
You're hence looking for something more like:
if exists( select * from sysobjects where name = 'Class')
drop table Class
go
create table Class
(
ClassDescription nvarchar(50) not null,
ClassID nchar(6) not null constraint pk_Class_ClassID primary key(ClassID)
)
go
create table Riders
(
RiderID int not null identity (10,1)
constraint pk_Riders_RiderID primary key(RiderID),
[Name] nvarchar(50) not null,
constraint chk_Riders_Name check (len(Name) > 4),
ClassID nchar(6) not null
constraint fk_Riders_Class foreign key
references Class(ClassID) on delete no action
)
go
answered Nov 25 '18 at 18:15
Caius JardCaius Jard
12k21240
edited Nov 25 '18 at 18:20
answered Nov 25 '18 at 18:15
Caius JardCaius Jard
12k21240
answered Nov 25 '18 at 18:15
Caius JardCaius Jard
12k21240
answered Nov 25 '18 at 18:15
Caius JardCaius Jard
12k21240
12k21240
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53470307%2fcreating-a-foreign-key-between-two-tables-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
WHy have you declared Riders to have a composite primary key?
– Caius Jard
Nov 25 '18 at 18:10
It did not recognize ClassID as a key when I tried to join it to Class. So I made both of them primary keys.
– V.M
Nov 25 '18 at 18:11
The FK must reference a complete key, not just one of its columns if it has two columns.
– jarlh
Nov 25 '18 at 18:13
What is ClassID? Is there a class table as well?
– jarlh
Nov 25 '18 at 18:14