Add Primary Key/Unique Keys/Foreign Keys After Table Creation Using SQL Query

Today, we will learn how to add Primary Key, Unique Keys, or Foreign Keys on a table after the table is created. We will be using a simple query.

Introduction

Today, we will learn how to add Primary Keys, Unique Keys, or Foreign Keys on a table after the table is created. We will be using a simple SQL query.

Sometimes, we find the below issues while working on keys,
  1. We have created a table but we didn't define the Primary Key of the table.
  2. We have created a table but didn't define the Unique Keys of the table.
  3. We created the table but didn't define the Foreign Keys of the table.
  4. A huge number of Unique Keys/Foreign Keys is there on a table. And, we need to create them.
  5. We need to replace the existing old Keys with some new keys.
  6. Most importantly, we don’t have the database access to create new keys on the Server by SSMS or UI.

I suggest you read my previous article before reading this one, which is also about the use of keys in SQL Server. If you read that article first, then, it will be easier to understand. I took the previous table's example here. Here is the link to the previous article.

I created the below tables.
 
tb_country
  1. CREATE TABLE [dbo].[tb_country](  
  2.     [country_id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [country_name] [nvarchar](50) NOT NULL,  
  4.     [isActive] [bitNOT NULL  
  5. ON [PRIMARY]  
  6.   
  7. GO  
tb_state
  1. CREATE TABLE [dbo].[tb_state](  
  2.     [state_id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [country_id] [intNOT NULL,  
  4.     [state_name] [nvarchar](50) NOT NULL,  
  5.     [isActive] [bitNOT NULL  
  6. ON [PRIMARY]  
  7.   
  8. GO  
tb_city
  1. CREATE TABLE [dbo].[tb_city](  
  2.     [city_id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [state_id] [intNOT NULL,  
  4.     [country_id] [intNOT NULL,  
  5.     [city_name] [nvarchar](50) NOT NULL,  
  6.     [isActive] [bitNOT NULL  
  7. ON [PRIMARY]  
  8.   
  9. GO  
Add Primary Key / Unique Keys / Foreign Keys after Table Creation By Query
 
Now, there are no keys and no key-combination between these tables because I haven't declared any key to them. While I require the below keys for them.
  1. Define [country_id] as Primary Key on [tb_country].
  2. Define [state_id] as Primary Key on [tb_state].
  3. Define [city_id] as Primary Key on [tb_city].
  4. Define Unique Key on [tb_country] with [country_name] field.
  5. Define Unique Key on [tb_state] with [country_id] and [state_name].
  6. Define Unique Key on [tb_city] with [country_id], [state_id] and [city_name].
  7. Define a Foreign Key on [tb_state] on [country_id] ( Use [country_id] from [tb_country] as primary).
  8. Define a Foreign Key on [tb_city] with [country_id] ( Use [country_id] from [tb_country] as primary).
  9. Define a Foreign Key on [tb_city] with [state_id] ( Use [state_id] from [tb_state] as primary)
Now, let us solve the above issues one by one by updating our tables using relevant SQL queries.
 
Define [country_id] as Primary Key on [tb_country],
  1. ALTER TABLE [tb_country]  
  2. ADD CONSTRAINT [PK_tb_country] PRIMARY KEY CLUSTERED   
  3. (  
  4.     [country_id] ASC  
  5. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  6. GO  
Define [state_id] as Primary Key on [tb_state],
  1. ALTER TABLE [tb_state]  
  2. ADD CONSTRAINT [PK_tb_state] PRIMARY KEY CLUSTERED   
  3. (  
  4.     [state_id] ASC  
  5. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  6. GO  
Define [city_id] as Primary Key on [tb_city],
  1. ALTER TABLE [dbo].[tb_city]  
  2. ADD CONSTRAINT [PK_tb_city] PRIMARY KEY CLUSTERED   
  3. (  
  4.     [city_id] ASC  
  5. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  6. GO  
Define Unique Key on [tb_country] with [country_name] field,
  1. ALTER TABLE [dbo].[tb_country]  
  2. ADD CONSTRAINT [IX_tb_country] UNIQUE NONCLUSTERED   
  3. (  
  4.     [country_name] ASC  
  5. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  6. GO  
Define Unique Key on [tb_state] with [country_id] and [state_name],
  1. ALTER TABLE [tb_state]  
  2. ADD CONSTRAINT [IX_tb_state] UNIQUE NONCLUSTERED   
  3. (  
  4.     [country_id] ASC,  
  5.     [state_name] ASC  
  6. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  7. GO  
Define Unique Key on [tb_city] with [country_id], [state_id] and [city_name],
  1. ALTER TABLE [dbo].[tb_city]  
  2. ADD CONSTRAINT [IX_tb_city] UNIQUE NONCLUSTERED   
  3. (  
  4.     [country_id] ASC,  
  5.     [state_id] ASC,  
  6.     [city_name] ASC  
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  8. GO  
Define a Foreign Key on [tb_state] on [country_id] ( Use [country_id] from [tb_country] as primary),
  1. ALTER TABLE [dbo].[tb_state]    
  2. ADD  CONSTRAINT [FK_tb_state_tb_country] FOREIGN KEY([country_id])  
  3. REFERENCES [dbo].[tb_country] ([country_id])  
  4. GO  
Define a Foreign Key on [tb_city] with [country_id] ( Use [country_id] from [tb_country] as primary),
  1. ALTER TABLE [dbo].[tb_city]    
  2. ADD  CONSTRAINT [FK_tb_city_tb_country] FOREIGN KEY([country_id])  
  3. REFERENCES [dbo].[tb_country] ([country_id])  
  4. GO  
Define a Foreign Key on [tb_city] with [state_id] ( Use [state_id] from [tb_state] as primary),
  1. ALTER TABLE [dbo].[tb_city]    
  2. ADD  CONSTRAINT [FK_tb_city_tb_state] FOREIGN KEY([state_id])  
  3. REFERENCES [dbo].[tb_state] ([state_id])  
  4. GO  

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

Add Primary Key / Unique Keys / Foreign Keys after Table Creation By Query 

 

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

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Add Primary Key / Unique Keys / Foreign Keys after Table Creation By Query
 
Summary
 
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.