Difference Between Primary Key and Foreign Key
You can be find what is Primary Key and Foreign Key and what are differences between Primary Key and Foreing Key below.
What is Primary Key?
- Primary Keys ensures that records in the table are unique. When you create a primary key on a column, a value inserted into that column can not be re-inserted. For example, you create a primary key in the ID column and you inserted a value of 1 to this column. You can not insert same value(1) to this column again. So, if you have a unique column, you can create a primary key on that column.
- A null value can not be inserted to Primary Key Column.
- If you create a primary key, SQL Server will create a clustered index by default. But you can force it to create nonclustered index. You may want to read the article named “Differences Between Clustered Index and Non Clustered Index“
- We can create only one Primary Key in a table.
- We can create Primary Key by combining multiple columns, This is called the Composite Key.
- Primary Keys can be related with Foreigns Keys
What is Foreign Key?
- Foreign Key is used to relate two tables to each other in relational databases.
- It can be created on a column or a combination of multiple columns. It is also referred to as a reference key.
- Foreign Keys have not to be unique.
- We can create more than one foreing key in the table.
- Sql Foreign Key values are related with Primary Key, which is located in a different table.
- If you create a Foreign Key, SQL Server will not create an index by default. But in terms of performance, you usually need to create an index on a foreign key column.
- In SQL databases, the primary key in one table and the foreign key in the other table are used to establish the relationship between the two tables.
- Unlike Primary Key, Foreing Key can be null.
Primary Key and Foreign Key Relationship
Another feature of Primary Keys is that it can be related with Foreing Keys.
For example, you create a primary key in the column named ID in the City Table. The other column name is CityName. Let’s imagine that there is another table called CityFeatures. We also have a column named CityID in the CityFeature table. This column contains information about feautes of Cities. We can create a Primary Key on the ID column of City table and we can relate this primary key with the Foreing Key on the CityID column of the CityFeatures table.
Thus, if you do not delete all records belonging to the relevant city in the CityFeatures table, you can not delete the record of the relevant city in the city table. This way we can protect our data integrity. Lets make an example practically.
Primary Key and Foreign Key Example
Create City and CityFeatures Tables
Let’s create the City and CityFeatures tables with 2 scripts below.
Create the City table with the following script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[City]( [ID] [int] IDENTITY(1,1) NOT NULL, [CityName] [varchar](250) NULL, CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO |
Create the CityFeatures table with the below script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CityFeatures]( [ID] [int] IDENTITY(1,1) NOT NULL, [CityID] [int] NOT NULL, [FamousCook] [varchar](50) NULL, [FamousDrink] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO |
Create a Foreing Key
With the following script, create a foreing key for the CityID column in the CityFeatures table, whose primary column is the ID column in the City table.
As you can see in the Script, we are creating a ForeingKey Constraint in the CityID column in the CityFeatures table. This foreign key’s primary key is the primary key we created on the ID column of City table.
|
1 2 3 4 5 |
ALTER TABLE [dbo].[CityFeatures] WITH CHECK ADD CONSTRAINT [FK_CityFeatures_City] FOREIGN KEY([CityID]) REFERENCES [dbo].[City] ([ID]) GO ALTER TABLE [dbo].[CityFeatures] CHECK CONSTRAINT [FK_CityFeatures_City] GO |
Insert Data to Tables
Add a record to the city table with the following script. As you can see, we just inserted value for CityName column. Because, we created ID column of City table as auto increment.. So when each record is inserted, the ID column will increase automatically.
|
1 |
INSERT INTO [dbo].[City]([CityName]) VALUES ('Newyork') |
Let’s try to add a record to CityFeatures as follows.
|
1 |
INSERT INTO [dbo].[CityFeatures]([CityID],[FamousCook],[FamousDrink]) VALUES (2,'x','y') |
When we run the above script, we will receive an error like below.
The Insert statement conflicted with the FOREIGN KEY constraint FK_CityFeatures_City. The conflict occured in database “Test”, table “dbo.City”, column ‘ID’.
The reason for the error is that there is no city with 2 IDs in the city table.
When we run the script as follows, we will see that the record has been inserted.
|
1 |
INSERT INTO [dbo].[CityFeatures]([CityID],[FamousCook],[FamousDrink]) VALUES (1,'x','y') |
When we try to delete the city table with the following script, we will receive an error like below.
|
1 2 3 4 |
USE [Test] GO DROP TABLE [dbo].[City] GO |
Could not drop object ‘dbo.City’ because it is referenced by a FOREIGN KEY constraint.
In order to delete the city table, we first need to delete the foreign key in the CityFeatures table.
Now, let’s try to delete the row with ID 1 in the city table by using below script.
|
1 2 3 4 |
USE [Test] GO DELETE FROM [dbo].[City] where ID=1 GO |
The script will fail as below, because there is no record with CityID value 1 in the CityFeatures table.
The DELETE statement conflicted with the REFERENCE constraint “FK_CityFeatures_City”.
If we first delete the values with ID 1 in the CityFeatures as follows and then run the above script again, delete script will successfully finish.
|
1 2 3 4 |
USE [Test] GO DELETE FROM [dbo].[CityFeatures] where CityID=1 GO |
As you can see in the example, we can guarantee our data integrity by using Primary Key and Foreign Key relationship.
Can primary keys be null?
No, primary keys can not be null. We have mentioned in the beginning of the article.
Can we have primary key on multiple columns?
Yes, we can create Primary Key by combining multiple columns, This is called the Composite Key.
Can a Primary Key be a Foreign Key?
If you want to create one-to-one relationship you can set foreign key as primary key. That is, setting the foreign key column as primary key may be contrary to the foreign key logic. Because usually we use foreign keys for one-to-many relationship.
Can a Primary Key have multiple Foreign Keys?
Yes, we can create multiple foreign keys for a primary key. But foreing keys must be on different tables. Because each foreign key reference same primary key value. Therefore, it doesnt make sense creating more than one foreign key for a same primary key. Because values of two foreign key columns will be same. Each foreing key column value that references same primary key can have different values, but it does not make sense too.
Consider our example, ID is our primary key and CityID is our foreign key. Suppose that we have a column CityID2 in CityFeatures table and you want to create a foreing key column on CityID2 that references ID column as primary key. You can do it. But why?
But, it makes more sense to create foreign key column on a different table that reference ID column in the City table.
Can we update primary key in SQL Server?
Yes we can update primary keys, but new value of our primary key must require primary key requirements. like uniqueness, not null, etc. Also, when creating the foreign key, you must create it with ON UPDATE CASCADE. If you specify “ON UPDATE CASCADE” when creating Foreign Key, this ensures that when there is an update in the column in the primary table, this update is reflected in the table with the foreign key. You may want to read the article “ON DELETE CASCADE and ON UPDATE CASCADE in SQL Server” for detailed information.
Important Note: Even if you create foreign key with ON UPDATE CASCADE, be careful that when updating primary key value. Because this cause updating all foreign key values and you may have millions of records in foreign key table.
If you do not create Foreign Key with ON UPDATE CASCADE and try to update primary key column you will receive the below error.
Msg 547, Level 16, State 0, Line 4
The UPDATE statement conflicted with the REFERENCE constraint “FK_CityFeatures_City”. The conflict occurred in database “Test”, table “dbo.CityFeatures”, column ‘CityID’.
The statement has been terminated.
Can we alter/change primary key data type?
We can not alter primary key column’s data type. If you try to alter the table as follows, you will receive the below error.
You can change primary key data type as follows;
- Drop all related foreign keys
- Change primary key column’s data type
- Change all related foreign keys column’s data type
- Create foreign key again.
Change Primary Key Data Type Example
Consider our example. We have a primary key on ID column in City table. Its data type is int. Lets change int to bigint.
Drop Foreign Key
|
1 2 3 4 |
USE [Test] GO ALTER TABLE [dbo].[CityFeatures] DROP CONSTRAINT [FK_CityFeatures_City] |
Change Primary Key Column’s Data Type
|
1 2 3 |
USE [Test] GO ALTER TABLE [dbo].[City] ALTER COLUMN [ID] bigint not null |
Change Foreign Key Column’s Data Type
|
1 2 3 |
USE [Test] GO ALTER TABLE [dbo].[CityFeatures] ALTER COLUMN [CityID] bigint not null |
Create Foreign Key on same column again
|
1 2 3 4 5 6 7 |
USE [Test] GO ALTER TABLE [dbo].[CityFeatures] WITH CHECK ADD CONSTRAINT [FK_CityFeatures_City] FOREIGN KEY([CityID]) REFERENCES [dbo].[City] ([ID]) ON UPDATE CASCADE GO ALTER TABLE [dbo].[CityFeatures] CHECK CONSTRAINT [FK_CityFeatures_City] GO |
Can foreign key be null?
Yes, If you create the foreign key column as null, you can create a Foreign Key on that column and you can insert null value to foreign key column. If you do not create foreign key column as null, you can not insert NULL values into foreign key columns. If you try to insert null values you will receive the error as follows.
|
1 |
INSERT INTO [dbo].[CityFeatures]([CityID],[FamousCook],[FamousDrink]) VALUES (null,'x','y') |
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column ‘CityID’, table ‘Test.dbo.CityFeatures’; column does not allow nulls. INSERT fails.
The statement has been terminated.
To insert null values to foreign key column, you must create table as follows;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CityFeatures]( [ID] [int] IDENTITY(1,1) NOT NULL, [CityID] [int] NULL, [FamousCook] [varchar](50) NULL, [FamousDrink] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO |
Can a foreign key be part of a primary key?
Yes, a foreing key be part of a primary key. Lets try it in our example. First delete CityFeatures table and create it again by using the below script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE [Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CityFeatures]( [ID] [int] IDENTITY(1,1) NOT NULL, [CityID] [int] NOT NULL, [FamousCook] [varchar](50) NULL, [FamousDrink] [varchar](50) NULL, PRIMARY KEY (ID, CityID), FOREIGN KEY (CityID) REFERENCES City(ID) ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO |
Can foreign key be duplicate?
Yes, Foreign Keys can be duplicate. They do not have to be unique. You can try to insert same insert value into CityFeatures table.
|
1 2 3 4 5 6 7 |
Declare @count int; SET @count=0; While (@count<10) BEGIN INSERT INTO [dbo].[CityFeatures]([CityID],[FamousCook],[FamousDrink]) VALUES (1,'x','z') SET @count=@count+1 END |
You may want to read the articles below.
“Differences Between Primary Key and Unique Constraint“,
“ON DELETE CASCADE and ON UPDATE CASCADE in SQL Server”
“Cannot truncate table because it is being referenced by a FOREIGN KEY constraint“,
“The constraint ‘PK_X’ is being referenced by table ‘X’, foreign key constraint ‘FK_X’“,
“The INSERT statement conflicted with the FOREIGN KEY constraint“,
“Could not drop object ” because it is referenced by a FOREIGN KEY constraint”
