Primary key Vs Foreign Key in SQL

As we know that relationship between two or more tables in SQL Server is the basic concept of any relational database. For an example. a family always starts with Parent and Children relationship, same as is a database always starts with product-item or customers-regions relationship. 

So, we will try to understand that how can we differentiate a relationship between two or more data tables in a database. You can suppose that Parent equals to Primary key and Children equal to Foreign key in a database.

In the above diagram, you can see the Primary Key and Foreign Key relationship between Students, Enrollments and Classes data tables. 
In Students data table, Student ID is the primary key and it is establishing a relationship with Enrollments tables and acts as a Foreign key. 

In Classes data table, Class ID is the primary key and it is establishing a relationship with Enrollments tables and acts as a Foreign key.

Primary Key - In a database, a table can have only one primary key which cannot have a NULL value. It always represents a clustered index in a database table and helps to organize the sequence of clustered index. 

Primary key can be related to another tables as a Foreign Key and you can apply Auto Increment value for a Primary key but auto increment is not mandatory.  

We can define Primary key constraint on temporary table and table variable.

Note: you can't delete primary key value from the parent table which is used as a foreign key in child table. To delete the primary key in the main table, you have to delete that primary key value from the child tables.

Foreign Key - A foreign key is just a referential constraint between two or more tables. If a Primary key is used in the another table then it would be know in the another table as Foreign key which can accept multiple null value. Foreign key always generates after Primary key. Thus they do not automatically increments. It supports  clustered or non-clustered indexes and you can have more than one foreign key in a table.

If you want to create some indexes on Foreign key then you must manually create an index on foreign keys. 

Please keep in your mind, you can’t create foreign key constraint on temporary table or table variable as compared to Primary key constraint.

You can delete the foreign key value from the child table even though that refers to the primary key of the parent table.

Foreign keys are almost always "Allow Duplicates," which would make them unsuitable as Primary Keys.
It is perfectly fine to use a foreign key as the primary key if the table is connected by a one-to-one relationship, not a one-to-many relationship.