Why is my create table failing? - does not match primary key

Question

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.

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 08: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 08:11
There is a post on this topic. Please find the link below: https://stackoverflow.com/questions/3996774/foreign-key-relationship-with-composite-primary-keys-in-sql-server-2005 — sumo, Mar 29 '18 at 08:14
@dnoeth That did allow the table to correctly create, but I am now getting a similar insert error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__VEHICLEREPORT__6477ECF3". The conflict occurred in database "SP18_CIS365_22830_team5", table "dbo.BILLING". — Synik, Mar 29 '18 at 08:18
Yep, what do you expect? You're trying to insert invalid data and the Foreign Key constraint prevens that. — dnoeth, Mar 29 '18 at 08:23
@dnoeth I'm honestly not sure. I've inserted ~6 tables to my DBMS tonight and this is the first wall I'm hitting and not sure how to fix. — Synik, Mar 29 '18 at 08:24
Since the types of bill# and vin# are identical, make sure that you're specifying them in the *same order* everywhere that you're defining them as keys. If you're not paying attention, you could easily now have set it up so that VEHICLEREPORT's vin# must match billings bill# and vice versa. — Damien_The_Unbeliever, Mar 29 '18 at 08:25
@Damien_The_Unbeliever I didn't know that the keys also have to be in the same order I thought just declaring them as PK/FK was enough. . In BILLING, the VIN# is before Bill#. I'm not sure how to change that unless I delete the entire table and just switch it around in a new create table statement. — Synik, Mar 29 '18 at 08:29
Since there's no requirement that the *names* match between the foreign key columns and the primary key columns, the only way to define what you want to match up is the column order. — Damien_The_Unbeliever, Mar 29 '18 at 08:32
@Damien_The_Unbeliever Man this table is a nightmare. I deleted the BILLING table, changed order to put BILL# at top, created the table again. I created this one, VehicleReport ok, but now I am getting truncation errors, all the sizes are the same :( — Synik, Mar 29 '18 at 08:51

score 1 · Accepted Answer · edited Nov 14 '18 at 02:19

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.

Why is my create table failing? - does not match primary key

1 Answers1