SQL Add foreign key to existing column

foreign-key-relationshipsql-server-2008

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

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

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

SQL Server 2008: The columns in table do not match an existing primary key or unique constraint

It means that the primary key in tblOne hasn't been properly declared - you need to go to tblOne and add the PRIMARY KEY constraint back onto it.

If you're sure that tblOne does have a PRIMARY KEY constraint, then maybe there are multiple tblOne tables in your DB, belonging to different schemas, and your references clause in your FK constraint is picking the wrong one.

If there's a composite key (which your comment would indicate), then you have to include both columns in your foreign key reference also. Note that a table can't have multiple primary keys - but if it has a composite key, you'll see a key symbol next to each column that is part of the primary key.

Best Answer

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2008: The columns in table do not match an existing primary key or unique constraint

Related Topic