Search code examples
sqlsql-servert-sqlforeign-keysalter-table

Foreign Key '... ' references invalid column '...' in referencing table '... '. SQL Server

I want to add a column to 'Customer' table which is FOREIGN KEY REFERENCING 'Test_Drive' table. I am using

ALTER TABLE Customer
ADD FOREIGN KEY (Test_Drive_ID) REFERENCES Test_Drive(Test_Drive_ID);

I think it should work properly on my tables. Below I send the Designs of them:

Customer Table

Test_Drive Table

I have no idea if I am blind, and do some spelling mistake, or something's wrong. I am grateful for any help :)

Msg 1769, Level 16, State 1, Line 98
Foreign key 'Test_Drive_ID' references invalid column 'Test_Drive_ID' in referencing table 'Customer'.

Msg 1750, Level 16, State 0, Line 98 Could not create constraint or index. See previous errors.

Solution

  • You want to add a column, along with the corresponding foreign key constraint:

    ALTER TABLE Customer
    ADD Test_Drive_ID INT
    REFERENCES Test_Drive(Test_Drive_ID);

    If you want to assign an explicit name to the FK:

    ALTER TABLE Customer
    ADD Test_Drive_ID INT
    CONSTRAINT FK_Customer_Test_Drive 
        FOREIGN KEY (Test_Drive_ID) 
        REFERENCES Test_Drive(Test_Drive_ID)
;