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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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

User · Answer

In the future    ALTER TABLE Employees ADD UserID int   ALTER TABLE Employees ADD CONSTRAINT FK ActiveDirectories UserID FOREIGN KEY  UserID      REFERENCES ActiveDirectories id

User · Answer

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

User · Answer

Maybe you got your columns backwards    ALTER TABLE Employees ADD FOREIGN KEY  UserID             lt -- this needs to be a column of the Employees table REFERENCES ActiveDirectories id     lt -- 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                     lt -- column in table  Employees  REFERENCES ActiveDirectories UserID     lt -- column in table  ActiveDirectories

[sql-server-2008] SQL Add foreign key to existing column

Examples related to sql-server-2008

Examples related to foreign-key-relationship