CODEWITHSUNDEEP
sql-server-2008foreign-key-relationship
ExceptionLimeCat
ExceptionLimeCat

Reputation: 6400

SQL Add foreign key to existing column

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

Upvotes: 131

Views: 377770

Answers (7)

Cleriston Pereira
Cleriston Pereira

Reputation: 9

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

Upvotes: 0

Krishneil
Krishneil

Reputation: 1490

In the future. 

ALTER TABLE Employees
ADD UserID int;

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

Upvotes: 3

Satyanand bhardwaj
Satyanand bhardwaj

Reputation: 355

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

Upvotes: 1

Siva Ramakrishna
Siva Ramakrishna

Reputation: 11

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

Upvotes: 0

Venkatesh Bandarapu
Venkatesh Bandarapu

Reputation: 401

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)

Upvotes: 5

marc_s
marc_s

Reputation: 754230

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"

Upvotes: 25

BluesRockAddict
BluesRockAddict

Reputation: 15683

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

Upvotes: 225

Related Questions