Transact sql error message Msg 1770 Level 16 - Foreign key references invalid column in referenced table - means that the column name is misspelled or do not exist.
Msg 1770 Level 16 Example:
USE model;
GO
CREATE TABLE students( id INT NOT NULL PRIMARY KEY, first_name CHAR(50),
last_name CHAR(50), gender CHAR(1), city CHAR(100), country CHAR(50), dep_id INT);
GO
CREATE TABLE departments( id INT NOT NULL PRIMARY KEY, name CHAR(250) NOT NULL);
GO
Invalid alter table:
USE model;
GO
ALTER TABLE students ADD FOREIGN KEY (dep_id) REFERENCES departments(id234) ;
GO
| Message |
|---|
| Msg 1770, Level 16, State 0, Line 1 |
| Foreign key 'FK__students__dep_id__18EBB532' references invalid column 'id234' in referenced table 'departments'. |
| Msg 1750, Level 16, State 0, Line 1 |
| Could not create constraint. See previous errors. |
Correct alter table:
USE model;
GO
ALTER TABLE students ADD FOREIGN KEY (dep_id) REFERENCES departments (id) ;
GO
| Message |
|---|
| Command(s) completed successfully. |
