Add Primary Key, Unique Keys, or Foreign Keys to a Table using SQL

CREATE TABLE [dbo].[tb_country](  
    [country_id] [int] IDENTITY(1,1) NOT NULL,  
    [country_name] [nvarchar](50) NOT NULL,  
    [isActive] [bit] NOT NULL  
) ON [PRIMARY]  
  
GO 

CREATE TABLE [dbo].[tb_state](  
    [state_id] [int] IDENTITY(1,1) NOT NULL,  
    [country_id] [int] NOT NULL,  
    [state_name] [nvarchar](50) NOT NULL,  
    [isActive] [bit] NOT NULL  
) ON [PRIMARY]  
  
GO

CREATE TABLE [dbo].[tb_city](  
    [city_id] [int] IDENTITY(1,1) NOT NULL,  
    [state_id] [int] NOT NULL,  
    [country_id] [int] NOT NULL,  
    [city_name] [nvarchar](50) NOT NULL,  
    [isActive] [bit] NOT NULL  
) ON [PRIMARY]  
  
GO

Define [country_id] as the Primary Key on [tb_country],

ALTER TABLE [tb_country]  
ADD CONSTRAINT [PK_tb_country] PRIMARY KEY CLUSTERED   
(  
    [country_id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
GO

Define [state_id] as Primary Key on [tb_state],

ALTER TABLE [tb_state]  
ADD CONSTRAINT [PK_tb_state] PRIMARY KEY CLUSTERED   
(  
    [state_id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
GO

Define [city_id] as Primary Key on [tb_city],

ALTER TABLE [dbo].[tb_city]  
ADD CONSTRAINT [PK_tb_city] PRIMARY KEY CLUSTERED   
(  
    [city_id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
GO

Define Unique Key on [tb_country] with [country_name] field,

ALTER TABLE [dbo].[tb_country]  
ADD CONSTRAINT [IX_tb_country] UNIQUE NONCLUSTERED   
(  
    [country_name] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
GO

Define Unique Key on [tb_state] with [country_id] and [state_name],

ALTER TABLE [tb_state]  
ADD CONSTRAINT [IX_tb_state] UNIQUE NONCLUSTERED   
(  
    [country_id] ASC,  
    [state_name] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
GO

Define Unique Key on [tb_city] with [country_id], [state_id] and [city_name],

ALTER TABLE [dbo].[tb_city]  
ADD CONSTRAINT [IX_tb_city] UNIQUE NONCLUSTERED   
(  
    [country_id] ASC,  
    [state_id] ASC,  
    [city_name] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
GO

Define a Foreign Key on [tb_state] on [country_id] ( Use [country_id] from [tb_country] as primary),

ALTER TABLE [dbo].[tb_state]    
ADD  CONSTRAINT [FK_tb_state_tb_country] FOREIGN KEY([country_id])  
REFERENCES [dbo].[tb_country] ([country_id])  
GO

Define a Foreign Key on [tb_city] with [country_id] ( Use [country_id] from [tb_country] as primary),

ALTER TABLE [dbo].[tb_city]    
ADD  CONSTRAINT [FK_tb_city_tb_country] FOREIGN KEY([country_id])  
REFERENCES [dbo].[tb_country] ([country_id])  
GO

Define a Foreign Key on [tb_city] with [state_id] ( Use [state_id] from [tb_state] as primary),

ALTER TABLE [dbo].[tb_city]    
ADD  CONSTRAINT [FK_tb_city_tb_state] FOREIGN KEY([state_id])  
REFERENCES [dbo].[tb_state] ([state_id])  
GO

Now, see your database diagram. The keys and references are created successfully.

You can find the keys on the database using the below query.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Summary

SQL Server supports ten types of database keys. You can add these keys to existing tables using SQL. In this article, we saw how to add a primary key, unique key, and a foreign key to an existing table. Issues which we discussed above are resolved successfully. If you have some alternative way to achieve this kind of requirement, please let me know. In case of a query or feedback, please leave your comments.