Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

ERROR Message- "Foreign Key '… ' references invalid column '…' in referencing table '… '." SQL Server

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)
;

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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*/

Or

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)
;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading