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'.

Excurved

Could you provide your two table's schema? – Thenna

refer this link #35197451 – Luwian

Answer 1

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

Tanning

This was correct. Our DB was not updating our add column. This has been resolved but not that our column is established I still can not add the constraint. There are no primary or candidate keys in the referenced table 'ActiveDirectories' that match the referencing column list in the foreign key 'FK__Employees__UserI__04E4BC85'. – Excurved

Looks like whichever column is referenced by FK__Employees__UserI__04E4BC85 is not defined as PRIMARY KEY or candidate key in ActiveDirectories table. – Tanning

yea but it is definitely our PK in the ActiveDirectories table – Excurved

RESOLVED: There is a reason why you create ERDs and establish relationships prior to building. We had too many records in one table which caused an error trying to create the relationships to the other table. Thanks all. – Excurved

refer this link #35197451 – Luwian

Thanks this worked, For future readers a Note: Please be sure that values of foreign keys of both the table match, otherwise you get this warning. – Ketty

You can add ON DELETE CASCADE ON UPDATE CASCADE – Delossantos

Answer 2

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" 

Clomb

I know its weird but unfortunately the name in the ActiveDirectory table id – Excurved

Answer 3

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)

Dit

Answer 4

In the future.

ALTER TABLE Employees
ADD UserID int;

ALTER TABLE Employees
ADD CONSTRAINT FK_ActiveDirectories_UserID FOREIGN KEY (UserID)
    REFERENCES ActiveDirectories(id);

Drunkometer

Answer 5

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

Washery

You should provide some explanation with your answer – Froissart

Answer 6

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

Antagonize

Answer 7

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

Ingrid