SQL Add foreign key to existing column
SQL Add foreign key to existing column
Question
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'.
Accepted Answer
Error indicates that there is no UserID column in your Employees table. Try adding the column first and then re-run the statement.
ALTER TABLE Employees
ADD CONSTRAINT FK_ActiveDirectories_UserID FOREIGN KEY (UserID)
REFERENCES ActiveDirectories(id);
Read more... Read less...
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"
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)
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
In the future.
ALTER TABLE Employees
ADD UserID int;
ALTER TABLE Employees
ADD CONSTRAINT FK_ActiveDirectories_UserID FOREIGN KEY (UserID)
REFERENCES ActiveDirectories(id);
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