ERROR Message- "Foreign Key '... ' references invalid column '...' in refer-CodePudding

I am using Microsoft SQL Server and trying to add a foreign key to the "Orders" table that references the primary key in my "Customer" table. I keep getting this message:

Msg 1769, Level 16, State 1, Line 28 Foreign key 'orders_customerid_fk' references invalid column 'CustomerID' in referencing table 'Orders'. Msg 1750, Level 16, State 0, Line 28 Could not create constraint or index. See previous errors.

create table Customer(
CustomerID INT NOT NULL PRIMARY KEY,
fName   varchar(40),
lName   varchar(40),
City    varchar(40),
Country varchar(40),
Phone   varchar(20)
);

create table Supplier(
SupplierID INT NOT NULL PRIMARY KEY,
CompanyName  varchar(40),
ContactName  varchar(50),
ContactTitle varchar(40),
City         varchar(40),
Country      varchar(40),
Phone        varchar(30),
Fax          varchar(30),
);

create table Orders(
OrderID INT NOT NULL PRIMARY KEY,
OrderDate     datetime,
OrderNumber   varchar(10),
TotalAmount   decimal(12,2)
);
        
ALTER TABLE Orders
    ADD CONSTRAINT Orders_CustomerID_FK
    FOREIGN KEY(CustomerID)
            REFERENCES Customer(CustomerID)
;

CodePudding user response：

You are missing the column that should receive the constrain of FK.

ALTER TABLE Orders
  ADD CustomerID INT NULL; /*Adds a new int column existing rows will be 
                     given a NULL value for the new column*/

ALTER TABLE Orders
  ADD CustomerID INT NOT NULL DEFAULT(0);

And then you can

ALTER TABLE Orders
    ADD CONSTRAINT Orders_CustomerID_FK
    FOREIGN KEY(CustomerID)
            REFERENCES Customer(CustomerID)
;