EN
MS SQL Server - add FOREIGN KEY to existing table
0 points
In this article, we would like to show you how to add FOREIGN KEY to the existing table in MS SQL Server.
Quick solution:
xxxxxxxxxx1
ALTER TABLE2
[table_name]3
ADD CONSTRAINT 4
[constraint_name] FOREIGN KEY ([column_name])5
REFERENCES [reference_table]([reference_column]);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.
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:
xxxxxxxxxx1
ALTER TABLE [users]2
ADD CONSTRAINT [fk_group_id] FOREIGN KEY ([group_id]) REFERENCES [groups]([id]);Result:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE [groups] (2
[id] INT IDENTITY(1,1),3
[group_name] VARCHAR(50) NOT NULL,4
PRIMARY KEY ([id])5
);6
7
CREATE TABLE [users] (8
[id] INT IDENTITY(1,1),9
[username] VARCHAR(50) NOT NULL,10
[group_id] INT,11
PRIMARY KEY ([id])12
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO [groups]2
([group_name])3
VALUES4
('group1'),5
('group2');6
7
INSERT INTO [users]8
([username], [group_id])9
VALUES10
('user1', 1),11
('user2', 1),12
('user3', 2),13
('user4', 2);