Introduction
Oftentimes, we need to add a key such as a primary key, unique key, or foreign key to an existing database table. We can do that using a SQL query. In this article, you'll learn how to add Primary Keys, Unique Keys, or Foreign Keys to a table after the table has already been created. We will be using a simple SQL query.
Sometimes, we find the below issues while working on keys,
- We have created a table but we didn't define the Primary Key of the table.
- We have created a table but didn't define the Unique Keys of the table.
- We created the table but didn't define the Foreign Keys of the table.
- A huge number of Unique Keys/Foreign Keys is there on a table. And, we need to create them.
- We need to replace the existing old Keys with some new keys.
- 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
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
tb_state
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
tb_city
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
![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.
- Define [country_id] as Primary Key on [tb_country].
- Define [state_id] as Primary Key on [tb_state].
- Define [city_id] as Primary Key on [tb_city].
- Define Unique Key on [tb_country] with [country_name] field.
- Define Unique Key on [tb_state] with [country_id] and [state_name].
- Define Unique Key on [tb_city] with [country_id], [state_id] and [city_name].
- Define a Foreign Key on [tb_state] on [country_id] ( Use [country_id] from [tb_country] as primary).
- Define a Foreign Key on [tb_city] with [country_id] ( Use [country_id] from [tb_country] as primary).
- 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 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.
![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
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.