What is the difference between SQL Primary and Foreign key

In this article I will give you details on primary and foreign key and will tell how they are differ from each other.

In SQL Server we have two keys a primary key and a foreign key which your might think that both are identical, but actually both are different in features and behaviours.

What is Primary Key?

A primary key is a particular relational table column or combination of columns designated to uniquely identify all table records.

Primary key should contain a unique value for each row of data. It cannot contain null values.

Primary key allows each row in a table to be uniquely identified and made sure that no duplicate rows exist and no null values are entered in the primary key column.

To manage relationship between two tables in the database primary key is necessary without having primary key in SQL we can not have relational database.

What is Foreign Key?

So Foreign key is basically a primary key from the another table.

We can have one or more columns in a table that refers to the primary key in another table.

So by defining foreign key in the table it specifies that the records from this table is associated with records from another table.

Key differences between primary and foreign key

Primary Key	Foreign Key
Primary key is unique record in the table.	Foreign key is a column in the table that is primary key in another table
Primary Key can not have null values.	Foreign key will accept multiple null value.
We can only have single Primary key in a Table	We can have multiple primary keys in a table.
Primary Key can not duplicate values.	Foreign key can have duplicate values
Primary key is automatically indexed	Where Foreign key does not create index by itself.

Read – How to Implement Laravel Eloquent One to One Relationship

Example of using Primary key and Foreign key

Let’s create users table, execute following query:

CREATE TABLE `users` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
)

Now lets add new posts table so it is going to be associated with user for example user can have multiple blog posts:

CREATE TABLE `posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `posts_user_id_foreign` (`user_id`),
  CONSTRAINT `posts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
)

Additionally add one more table just so you understand that primary key can attach to multiple tables as foreign key:

CREATE TABLE `profiles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `website_url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `facebook_url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `github_url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `profiles_user_id_foreign` (`user_id`),
  CONSTRAINT `profiles_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
)

Now if you see your database relationship this how it is going to look alike:

That’s all about primary and foreign key if your laravel developer I would suggest to read following article on Eloquent database relationships:

Read – How to Implement Laravel Eloquent One-to-Many Relationship