There are no primary or candidate keys in the referenced table error

Question

I have a problem with combining foreign keys to different tables. For example I made a table Customers and a Table invoices. I want a foreign key from Customers to invoices so I can get the name and everything of the Customer:

Create table code of Customers:

Create Table Customers
(
    customerID int IDENTITY(100,1) NOT NULL, 
    customer_email varchar(30) NOT NULL,
    username varchar(255) NOT NULL,
    password varchar(50) NOT NULL,
    firstname varchar(255) NOT NULL,
    lastname varchar(255) NOT NULL,
    insertion varchar(10)   NULL,
    phonenumber int NULL,
    streetname varchar(20) NOT NULL,
    number  int NOT NULL,
    zipcode varchar(10) NOT NULL,
    city    varchar(255) NOT NULL,

    Constraint pk_Customers 
       PRIMARY KEY (customerID, customer_email, username)
)

Create table code of Invoices:

Create Table Invoices 
(
    invoiceID int IDENTITY(1000,1) NOT NULL,
    customer_email varchar(30) NOT NULL,
    customerID int NOT NULL,
    creationdate datetime NOT NULL DEFAULT GETDATE(),
    totalAmount decimal(5,2) NOT NULL,

    Constraint pk_Invoices 
        PRIMARY KEY (invoiceID, customer_email,creationdate)
)

The foreign key code that I want to use:

ALTER Table Invoices
ADD Constraint fk_Customers_Invoices 
FOREIGN KEY (customerID) REFERENCES Customers (customerID)
   ON UPDATE CASCADE 
   ON DELETE NO ACTION

It throws the following error:

There are no primary or candidate keys in the referenced table 'Customers' that match the referencing column list in the foreign key 'fk_Customers_Invoices'.

How can I add my foreign key?

A bit odd structure. If `CustomerID` is an `identity` column then why are there any other columns in `PRIMARY KEY`? Both of your PKs must by on identity col. I can't even imagine what would be the purpose of unique constraint on **customer_email+creationdate**... You are getting an error because your PK consists of three columns but you are attempting to reference it with only one column. One column is not enough to locate record which is identified by three columns. Fix your PKs as I mentioned above and FK will work fine. — Ivan Starostin, Feb 25 '16 at 10:34
possible duplicate of http://stackoverflow.com/questions/3996774/foreign-key-relationship-with-composite-primary-keys-in-sql-server-2005 — steoleary, Feb 25 '16 at 10:38

score 2 · Answer 1 · answered Feb 25 '16 at 10:30

I think it's because your primary key is a composite key:

customerID, customer_email, username

This suggests that it is only the combination of these three fields that will uniquely identify a customer, and the foreign key would need to reference all three fields.

If customerID is unique, then it should be the primary key for the table and your foreign keys would be able to use it as a reference.

For what purpose are the other fields included in the primary key?

score 1 · Accepted Answer · answered Feb 25 '16 at 10:30

1

Since your Customers table defines this primary key:

Constraint pk_Customers 
   PRIMARY KEY (customerID, customer_email, username)

any table that wants to reference Customers must provide all three columns of that primary key. That's the way FK constraints work.

So from your Invoices tables, you must provide all 3 columns that make up the primary key of Customers - not just one. You can never refernce only part of a primary key - it's the whole key or nothing....

ALTER Table Invoices
ADD Constraint fk_Customers_Invoices 
FOREIGN KEY (customerID) REFERENCES Customers (customerID)
   ON UPDATE CASCADE 
   ON DELETE NO ACTION

You can either:

change the PK for Customers to be just CustomerID which would make a whole lot more sense since it's an identity column
or you could add the other two columns in the Customers PK to yoru table Invoices

answered Feb 25 '16 at 10:30

marc_s

732,580
175
1,330
1,459

But with this i can still use the function UNIQUE right? – Carsten Flokstra Feb 25 '16 at 13:12
@CarstenFlokstra: not sure what you refer to - `UNIQUE` on / for what ?!?!? – marc_s Feb 25 '16 at 13:31
That there are unique values in the database with customer_email and username. – Carsten Flokstra Feb 29 '16 at 08:53
It is strange to have copies of usernames en customer_email adresses. – Carsten Flokstra Feb 29 '16 at 08:56
@CarstenFlokstra: well in that case, make the `CustomerID` **alone** the PK! That makes the most sense. And then you don't have "copies" of "user names" and "customer_email" addresses in other tables either.... to ensure uniqueness for `(username, customer_email)`, you can always put an **UNIQUE CONSTRAINT** on those two columns - they don't need to be part of the PK ! – marc_s Feb 29 '16 at 11:27

There are no primary or candidate keys in the referenced table error

2 Answers2