MS SQL Server - add FOREIGN KEY to existing table

In this article, we would like to show you how to add FOREIGN KEY to the existing table in MS SQL Server.

Quick solution:

ALTER TABLE
    [table_name]
ADD CONSTRAINT 
    [constraint_name] FOREIGN KEY ([column_name])
    REFERENCES [reference_table]([reference_column]);

Practical example

To show how to add FOREIGN KEY to the existing table, we will use the following tables

Note:

At the end of this article you can find database preparation SQL queries.

Example

In this example, we will add fk_group_id foreign key to the group_id column from users table which is a reference to the id column from groups table.

Query:

ALTER TABLE [users]
ADD CONSTRAINT [fk_group_id] FOREIGN KEY ([group_id]) REFERENCES [groups]([id]);

Result:

Database preparation

create_tables.sql file:

CREATE TABLE [groups] (
	[id] INT IDENTITY(1,1),
	[group_name] VARCHAR(50) NOT NULL,
	PRIMARY KEY ([id])
);

CREATE TABLE [users] (
	[id] INT IDENTITY(1,1),
	[username] VARCHAR(50) NOT NULL,
	[group_id] INT,
	PRIMARY KEY ([id])
);

insert_data.sql file:

INSERT INTO [groups]
    ([group_name])
VALUES
    ('group1'),
	 ('group2');

INSERT INTO [users]
    ([username], [group_id])
VALUES
    ('user1', 1),
    ('user2', 1),
    ('user3', 2),
	('user4', 2);