Difference between Primary Key and Foreign Key
In SQL Server, there are two keys - primary key and foreign key which seems identical, but actually both are different in features and behaviours. In this article, I would like to share the key differences between primary key and foreign key.
For more information about the keys, please refer to the article Different Types of SQL Keys.
Difference between Primary Key and Foreign Key
Defining Primary key and Foreign key
--Create Parent Table CREATE TABLE Department ( DeptID int PRIMARY KEY, --define primary key Name varchar (50) NOT NULL, Address varchar(100) NULL ) GO --Create Child Table CREATE TABLE Employee ( EmpID int PRIMARY KEY, --define primary key Name varchar (50) NOT NULL, Salary int NULL, --define foreign key DeptID int FOREIGN KEY REFERENCES Department(DeptID) )
Important Note
As @Marc Jellinek suggested, I would like to add the below points about foreign key :
Foreign keys do not automatically create an index, clustered or non-clustered. You must manually create an index on foreign keys.
There are actual advantages to having a foreign key be supported with a clustered index, but you get only one per table. What's the advantage? If you are selecting the parent plus all child records, you want the child records next to each other. This is easy to accomplish using a clustered index.
Having a null foreign key is usually a bad idea. In the example below, the record in [dbo].[child] is what would be referred to as an "orphan record". Think long and hard before doing this.
Droping Database Tables
IF EXISTS (SELECT * FROM [sys].[schemas] [sch] INNER JOIN [sys].[tables] [tbl] ON [sch].[schema_id] = [tbl].[schema_id] WHERE [sch].[name] = 'dbo' AND [tbl].[name] = 'child') DROP TABLE [dbo].[child] IF EXISTS (SELECT * FROM [sys].[schemas] [sch] INNER JOIN [sys].[tables] [tbl] ON [sch].[schema_id] = [tbl].[schema_id] WHERE [sch].[name] = 'dbo' AND [tbl].[name] = 'parent') DROP TABLE [dbo].[parent]
Creating Indexes on Tables
CREATE TABLE [dbo].[parent]
(
[id] [int] IDENTITY NOT NULL,
[name] [varchar](250) NOT NULL,
CONSTRAINT [PK_dbo__parent] PRIMARY KEY NONCLUSTERED ([id])
)
CREATE TABLE [dbo].[child]
(
[id] [int] IDENTITY NOT NULL, [parent_id] [int] NULL,
[name] [varchar](250) NOT NULL,
CONSTRAINT [PK_dbo__child] PRIMARY KEY NONCLUSTERED ([id]),
CONSTRAINT [FK_dbo__child__dbo__parent] FOREIGN KEY ([parent_id]) REFERENCES [dbo].[parent]([id])
)
--Insert data
INSERT INTO [dbo].[parent] ([name]) VALUES ('parent1')
INSERT INTO [dbo].[child] ([parent_id], [name])VALUES(1, 'child 1')
INSERT INTO [dbo].[child] ([parent_id], [name])VALUES(NULL, 'child 2')
--Select data
SELECT * FROM [dbo].[child]
Summary
I hope you will enjoy these tricks while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.
