向现有列添加外键

如果在 SQLServer2008中使用以下 SQL 命令更新具有外键约束的表:

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

UserID 是我在 Employees表中的 FK 列。我正在尝试引用 ActiveDirectories表中的 UserID。我收到这个错误:

外键“ UserID”在引用中引用无效的列“ UserID” 表’员工’。

350419 次浏览
小开
最佳答案

错误指示“雇员”表中没有 UserID 列。尝试首先添加列，然后重新运行该语句。

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

也许你把你的专栏倒过来了？

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

这个列在 Employees表中被称为 ID，在 ActiveDirectories表中被称为 UserID吗？

那么你的命令应该是:

ALTER TABLE Employees
ADD FOREIGN KEY (ID)                   <-- column in table "Employees"
REFERENCES ActiveDirectories(UserID)   <-- column in table "ActiveDirectories"
小开

MySQL/SQL Server/Oracle/MS 访问:

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

若要允许对 FOREIGNKEY 约束进行命名，并在多列上定义 FOREIGNKEY 约束，请使用以下 SQL 语法:

MySQL/SQL Server/Oracle/MS 访问:

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_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

小开 
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
小开

在未来。

ALTER TABLE Employees
ADD UserID int;


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

If the table has already been created:

第一件事:

ALTER TABLE `table1_name` ADD UNIQUE( `column_name`);

然后:

ALTER TABLE `table1_name` ADD FOREIGN KEY (`column_name`) REFERENCES `table2_name`(`column_name`);

提交答案