Why is my create table failing? - does not match primary key
This is what I am trying to create:
CREATE TABLE VEHICLEREPORT
(
DeptID char(2) not null,
Vin# char(3) not null,
Miles varchar(6) not null,
Bill# char(3) not null,
EID char(3) not null,
PRIMARY KEY (DeptID, Vin#),
FOREIGN KEY (bill#) REFERENCES billing,
FOREIGN KEY (EID) REFERENCES Employee
);
The issue is with my reference to billing. The error says:
The number of columns in the referencing column list for foreign key 'FK__VEHICLERE__Bill#__5AEE82B9' does not match those of the primary key in the referenced table 'Billing'.
but my billing table entered fine:
CREATE TABLE BILLING
(
VIN# char(3),
BILL# char(3),
PRIMARY KEY (VIN#, Bill#),
FOREIGN KEY (VIN#) REFERENCES vehicle
);
What am i missing with this?
Appreciate the help.
sql
sql-server edited Mar 29 '18 at 8:06
marc_s
576k12811111258
asked Mar 29 '18 at 8:02
SynikSynik
315
|
show 7 more comments
This is what I am trying to create:
CREATE TABLE VEHICLEREPORT
(
DeptID char(2) not null,
Vin# char(3) not null,
Miles varchar(6) not null,
Bill# char(3) not null,
EID char(3) not null,
PRIMARY KEY (DeptID, Vin#),
FOREIGN KEY (bill#) REFERENCES billing,
FOREIGN KEY (EID) REFERENCES Employee
);
The issue is with my reference to billing. The error says:
The number of columns in the referencing column list for foreign key 'FK__VEHICLERE__Bill#__5AEE82B9' does not match those of the primary key in the referenced table 'Billing'.
but my billing table entered fine:
CREATE TABLE BILLING
(
VIN# char(3),
BILL# char(3),
PRIMARY KEY (VIN#, Bill#),
FOREIGN KEY (VIN#) REFERENCES vehicle
);
What am i missing with this?
Appreciate the help.
sql
sql-server edited Mar 29 '18 at 8:06
marc_s
576k12811111258
asked Mar 29 '18 at 8:02
SynikSynik
315
10
BILLING has a two column primary key. You're attempting to create a foreign key reference to it using a single column. The error message pretty well already told you that. How to correct it depends on what the data structure is meant to actually be - should BILLING actually have a single column PK, or should the FK be using VIN# as well as bill#?
– Damien_The_Unbeliever
Mar 29 '18 at 8:05
4
That's one of the clearest error messages I've seen.
– HoneyBadger
Mar 29 '18 at 8:05
Despite it being pretty clear, I cant figure out how to fix it. BILLING should have both VIN# and Bill# as the PK. Even when adding VIN# to the FK in the code, the reference billing still errors :(
– Synik
Mar 29 '18 at 8:11
FOREIGN KEY (VIN#,Bill#) REFERENCES billing
– dnoeth
Mar 29 '18 at 8:14
1
There is a post on this topic. Please find the link below: stackoverflow.com/questions/3996774/…
– sumo
Mar 29 '18 at 8:14
|
show 7 more comments
This is what I am trying to create:
CREATE TABLE VEHICLEREPORT
(
DeptID char(2) not null,
Vin# char(3) not null,
Miles varchar(6) not null,
Bill# char(3) not null,
EID char(3) not null,
PRIMARY KEY (DeptID, Vin#),
FOREIGN KEY (bill#) REFERENCES billing,
FOREIGN KEY (EID) REFERENCES Employee
);
The issue is with my reference to billing. The error says:
The number of columns in the referencing column list for foreign key 'FK__VEHICLERE__Bill#__5AEE82B9' does not match those of the primary key in the referenced table 'Billing'.
but my billing table entered fine:
CREATE TABLE BILLING
(
VIN# char(3),
BILL# char(3),
PRIMARY KEY (VIN#, Bill#),
FOREIGN KEY (VIN#) REFERENCES vehicle
);
What am i missing with this?
Appreciate the help.
sql
sql-server edited Mar 29 '18 at 8:06
marc_s
576k12811111258
asked Mar 29 '18 at 8:02
SynikSynik
315
This is what I am trying to create:
CREATE TABLE VEHICLEREPORT
(
DeptID char(2) not null,
Vin# char(3) not null,
Miles varchar(6) not null,
Bill# char(3) not null,
EID char(3) not null,
PRIMARY KEY (DeptID, Vin#),
FOREIGN KEY (bill#) REFERENCES billing,
FOREIGN KEY (EID) REFERENCES Employee
);
The issue is with my reference to billing. The error says:
The number of columns in the referencing column list for foreign key 'FK__VEHICLERE__Bill#__5AEE82B9' does not match those of the primary key in the referenced table 'Billing'.
but my billing table entered fine:
CREATE TABLE BILLING
(
VIN# char(3),
BILL# char(3),
PRIMARY KEY (VIN#, Bill#),
FOREIGN KEY (VIN#) REFERENCES vehicle
);
What am i missing with this?
Appreciate the help.
sql
sql-server sql
sql-server edited Mar 29 '18 at 8:06
marc_s
576k12811111258
asked Mar 29 '18 at 8:02
SynikSynik
315
edited Mar 29 '18 at 8:06
marc_s
576k12811111258
asked Mar 29 '18 at 8:02
SynikSynik
315
edited Mar 29 '18 at 8:06
marc_s
576k12811111258
edited Mar 29 '18 at 8:06
marc_s
576k12811111258
edited Mar 29 '18 at 8:06
marc_s
576k12811111258
576k12811111258
asked Mar 29 '18 at 8:02
SynikSynik
315
asked Mar 29 '18 at 8:02
SynikSynik
315
asked Mar 29 '18 at 8:02
SynikSynik
315
315
10
BILLING has a two column primary key. You're attempting to create a foreign key reference to it using a single column. The error message pretty well already told you that. How to correct it depends on what the data structure is meant to actually be - should BILLING actually have a single column PK, or should the FK be using VIN# as well as bill#?
– Damien_The_Unbeliever
Mar 29 '18 at 8:05
4
That's one of the clearest error messages I've seen.
– HoneyBadger
Mar 29 '18 at 8:05
Despite it being pretty clear, I cant figure out how to fix it. BILLING should have both VIN# and Bill# as the PK. Even when adding VIN# to the FK in the code, the reference billing still errors :(
– Synik
Mar 29 '18 at 8:11
FOREIGN KEY (VIN#,Bill#) REFERENCES billing
– dnoeth
Mar 29 '18 at 8:14
1
There is a post on this topic. Please find the link below: stackoverflow.com/questions/3996774/…
– sumo
Mar 29 '18 at 8:14
|
show 7 more comments
10
BILLING has a two column primary key. You're attempting to create a foreign key reference to it using a single column. The error message pretty well already told you that. How to correct it depends on what the data structure is meant to actually be - should BILLING actually have a single column PK, or should the FK be using VIN# as well as bill#?
– Damien_The_Unbeliever
Mar 29 '18 at 8:05
4
That's one of the clearest error messages I've seen.
– HoneyBadger
Mar 29 '18 at 8:05
Despite it being pretty clear, I cant figure out how to fix it. BILLING should have both VIN# and Bill# as the PK. Even when adding VIN# to the FK in the code, the reference billing still errors :(
– Synik
Mar 29 '18 at 8:11
FOREIGN KEY (VIN#,Bill#) REFERENCES billing
– dnoeth
Mar 29 '18 at 8:14
1
There is a post on this topic. Please find the link below: stackoverflow.com/questions/3996774/…
– sumo
Mar 29 '18 at 8:14
10
10
BILLING has a two column primary key. You're attempting to create a foreign key reference to it using a single column. The error message pretty well already told you that. How to correct it depends on what the data structure is meant to actually be - should BILLING actually have a single column PK, or should the FK be using VIN# as well as bill#?
– Damien_The_Unbeliever
Mar 29 '18 at 8:05
BILLING has a two column primary key. You're attempting to create a foreign key reference to it using a single column. The error message pretty well already told you that. How to correct it depends on what the data structure is meant to actually be - should BILLING actually have a single column PK, or should the FK be using VIN# as well as bill#?
– Damien_The_Unbeliever
Mar 29 '18 at 8:05
4
4
That's one of the clearest error messages I've seen.
– HoneyBadger
Mar 29 '18 at 8:05
That's one of the clearest error messages I've seen.
– HoneyBadger
Mar 29 '18 at 8:05
Despite it being pretty clear, I cant figure out how to fix it. BILLING should have both VIN# and Bill# as the PK. Even when adding VIN# to the FK in the code, the reference billing still errors :(
– Synik
Mar 29 '18 at 8:11
Despite it being pretty clear, I cant figure out how to fix it. BILLING should have both VIN# and Bill# as the PK. Even when adding VIN# to the FK in the code, the reference billing still errors :(
– Synik
Mar 29 '18 at 8:11
FOREIGN KEY (VIN#,Bill#) REFERENCES billing– dnoeth
Mar 29 '18 at 8:14
FOREIGN KEY (VIN#,Bill#) REFERENCES billing– dnoeth
Mar 29 '18 at 8:14
1
1
There is a post on this topic. Please find the link below: stackoverflow.com/questions/3996774/…
– sumo
Mar 29 '18 at 8:14
There is a post on this topic. Please find the link below: stackoverflow.com/questions/3996774/…
– sumo
Mar 29 '18 at 8:14
|
show 7 more comments
1 Answer
1
active
oldest
votes
If you think of the foreign key as establishing a parent-child relationship between two tables, then the parent side column(s) need to be unique.
From Wikipedia:
In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. ... In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
In your example, there is no guarantee that VIN# is unique in VEHICLEREPORT. Below are your options
- VIN# is guaranteed to be unique in VEHICLEREPORT. In this case add a UNIQUE constraint on VIN# on the VEHICLEREPORT table. The error will go away.
- VIN# is not unique in VEHICLEREPORT (doesn't seem likely). If this is the case, then likely there is a flaw in the design of your BILLING table as it could likely point to more than one row in VEHICLEREPORT. You should consider adding DeptID column to BILLING and creating a composite foreign key.
Also if VIN# is unique (case 1 above), you should think of why DeptID is present in the PK. Maybe the right fix at the end is to drop DeptID from the primary key.
edited Nov 14 '18 at 2:19
Pang
6,9011664102
answered Mar 29 '18 at 8:43
RnPRnP
3608
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%2f49550904%2fwhy-is-my-create-table-failing-does-not-match-primary-key%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
If you think of the foreign key as establishing a parent-child relationship between two tables, then the parent side column(s) need to be unique.
From Wikipedia:
In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. ... In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
In your example, there is no guarantee that VIN# is unique in VEHICLEREPORT. Below are your options
- VIN# is guaranteed to be unique in VEHICLEREPORT. In this case add a UNIQUE constraint on VIN# on the VEHICLEREPORT table. The error will go away.
- VIN# is not unique in VEHICLEREPORT (doesn't seem likely). If this is the case, then likely there is a flaw in the design of your BILLING table as it could likely point to more than one row in VEHICLEREPORT. You should consider adding DeptID column to BILLING and creating a composite foreign key.
Also if VIN# is unique (case 1 above), you should think of why DeptID is present in the PK. Maybe the right fix at the end is to drop DeptID from the primary key.
edited Nov 14 '18 at 2:19
Pang
6,9011664102
answered Mar 29 '18 at 8:43
RnPRnP
3608
add a comment |
If you think of the foreign key as establishing a parent-child relationship between two tables, then the parent side column(s) need to be unique.
From Wikipedia:
In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. ... In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
In your example, there is no guarantee that VIN# is unique in VEHICLEREPORT. Below are your options
- VIN# is guaranteed to be unique in VEHICLEREPORT. In this case add a UNIQUE constraint on VIN# on the VEHICLEREPORT table. The error will go away.
- VIN# is not unique in VEHICLEREPORT (doesn't seem likely). If this is the case, then likely there is a flaw in the design of your BILLING table as it could likely point to more than one row in VEHICLEREPORT. You should consider adding DeptID column to BILLING and creating a composite foreign key.
Also if VIN# is unique (case 1 above), you should think of why DeptID is present in the PK. Maybe the right fix at the end is to drop DeptID from the primary key.
edited Nov 14 '18 at 2:19
Pang
6,9011664102
answered Mar 29 '18 at 8:43
RnPRnP
3608
add a comment |
If you think of the foreign key as establishing a parent-child relationship between two tables, then the parent side column(s) need to be unique.
From Wikipedia:
In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. ... In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
In your example, there is no guarantee that VIN# is unique in VEHICLEREPORT. Below are your options
- VIN# is guaranteed to be unique in VEHICLEREPORT. In this case add a UNIQUE constraint on VIN# on the VEHICLEREPORT table. The error will go away.
- VIN# is not unique in VEHICLEREPORT (doesn't seem likely). If this is the case, then likely there is a flaw in the design of your BILLING table as it could likely point to more than one row in VEHICLEREPORT. You should consider adding DeptID column to BILLING and creating a composite foreign key.
Also if VIN# is unique (case 1 above), you should think of why DeptID is present in the PK. Maybe the right fix at the end is to drop DeptID from the primary key.
edited Nov 14 '18 at 2:19
Pang
6,9011664102
answered Mar 29 '18 at 8:43
RnPRnP
3608
If you think of the foreign key as establishing a parent-child relationship between two tables, then the parent side column(s) need to be unique.
From Wikipedia:
In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. ... In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table.
In your example, there is no guarantee that VIN# is unique in VEHICLEREPORT. Below are your options
- VIN# is guaranteed to be unique in VEHICLEREPORT. In this case add a UNIQUE constraint on VIN# on the VEHICLEREPORT table. The error will go away.
- VIN# is not unique in VEHICLEREPORT (doesn't seem likely). If this is the case, then likely there is a flaw in the design of your BILLING table as it could likely point to more than one row in VEHICLEREPORT. You should consider adding DeptID column to BILLING and creating a composite foreign key.
Also if VIN# is unique (case 1 above), you should think of why DeptID is present in the PK. Maybe the right fix at the end is to drop DeptID from the primary key.
edited Nov 14 '18 at 2:19
Pang
6,9011664102
answered Mar 29 '18 at 8:43
RnPRnP
3608
edited Nov 14 '18 at 2:19
Pang
6,9011664102
edited Nov 14 '18 at 2:19
Pang
6,9011664102
edited Nov 14 '18 at 2:19
Pang
6,9011664102
6,9011664102
answered Mar 29 '18 at 8:43
RnPRnP
3608
answered Mar 29 '18 at 8:43
RnPRnP
3608
answered Mar 29 '18 at 8:43
RnPRnP
3608
3608
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%2f49550904%2fwhy-is-my-create-table-failing-does-not-match-primary-key%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
10
BILLING has a two column primary key. You're attempting to create a foreign key reference to it using a single column. The error message pretty well already told you that. How to correct it depends on what the data structure is meant to actually be - should BILLING actually have a single column PK, or should the FK be using VIN# as well as bill#?
– Damien_The_Unbeliever
Mar 29 '18 at 8:05
4
That's one of the clearest error messages I've seen.
– HoneyBadger
Mar 29 '18 at 8:05
Despite it being pretty clear, I cant figure out how to fix it. BILLING should have both VIN# and Bill# as the PK. Even when adding VIN# to the FK in the code, the reference billing still errors :(
– Synik
Mar 29 '18 at 8:11
FOREIGN KEY (VIN#,Bill#) REFERENCES billing– dnoeth
Mar 29 '18 at 8:14
1
There is a post on this topic. Please find the link below: stackoverflow.com/questions/3996774/…
– sumo
Mar 29 '18 at 8:14