SQL Add foreign key to existing column

sql-server-2008 foreign-key-relationship
ExceptionLimeCat picture ExceptionLimeCat · Apr 30, 2012 · Viewed 316k times · Source

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

Answer

BluesRockAddict picture BluesRockAddict · Apr 30, 2012

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

Related questions

How to disable Constraints for all the tables and enable it?

I have 60 tables. I want to drop 10 tables where these 10 tables are Constraints(PK,FK) to other 20 tables. While dropping these 10 tables, I need to truncate or delete data from the other 20 tables. Finally I want to disable all 60 table …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More