If I am using the following SQL command in SQL Server 2008 to update a table with a foreign key constraint:
ALTER TABLE Employees
ADD FOREIGN KEY (UserID)
REFERENCES ActiveDirectories(id)
UserID
being my FK column in the Employees
table. I'm trying to reference the UserID
in my ActiveDirectories
table. I receive this error:
Foreign key 'UserID' references invalid column 'UserID' in referencing table 'Employees'.
This question is related to
sql-server-2008
foreign-key-relationship
If the table has already been created:
First do:
ALTER TABLE `table1_name` ADD UNIQUE( `column_name`);
Then:
ALTER TABLE `table1_name` ADD FOREIGN KEY (`column_name`) REFERENCES `table2_name`(`column_name`);
ALTER TABLE Faculty
WITH CHECK ADD CONSTRAINT FKFacultyBook
FOREIGN KEY FacId
REFERENCES Book Book_Id
ALTER TABLE Faculty
WITH CHECK ADD CONSTRAINT FKFacultyStudent
FOREIGN KEY FacId
REFERENCES Student StuId
Maybe you got your columns backwards??
ALTER TABLE Employees
ADD FOREIGN KEY (UserID) <-- this needs to be a column of the Employees table
REFERENCES ActiveDirectories(id) <-- this needs to be a column of the ActiveDirectories table
Could it be that the column is called ID
in the Employees
table, and UserID
in the ActiveDirectories
table?
Then your command should be:
ALTER TABLE Employees
ADD FOREIGN KEY (ID) <-- column in table "Employees"
REFERENCES ActiveDirectories(UserID) <-- column in table "ActiveDirectories"
way of foreign key creation correct for ActiveDirectories(id), i think the main mistake is you didn't mentioned primary key for id in ActiveDirectories table
In the future.
ALTER TABLE Employees
ADD UserID int;
ALTER TABLE Employees
ADD CONSTRAINT FK_ActiveDirectories_UserID FOREIGN KEY (UserID)
REFERENCES ActiveDirectories(id);
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
Source: Stackoverflow.com