Foreign key is a column or field in current table and primary key of another table, the foreign key points to another table using primary key of that table.

Points to remember:

1.  Ensure value added in foreign key column exists in primary key table, otherwise a foreign key violation      error message is returned.
2.  While adding foreign key specify NOT NULL on all the participating columns, otherwise a foreign key violation error message is returned.
3.  FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference
4.  A column of type varchar(max) can participate in a FOREIGN KEY constraint only if the primary key it references is also defined as type varchar(max).
5.  The Database Engine does not have a predefined limit on either the number of FOREIGN KEY constraints, but we recommend using maximum 253 constraints.

Let’s explain with example:

Column Level

USE [WebShop]
GO
CREATE TABLE [dbo].[Products]
(
            [ProductID] [int] IDENTITY(1,1) NOT NULL Primary Key,
            [ProductName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](max) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NULL,
            --Column level foreign key ,pointing to categories table 
            [CategoryID] [int] NULL CONSTRAINT [FK_Products_Categories] FOREIGN KEY   REFERENCES Categories(CategoryID)
 )
GO

Table Level

USE [WebShop]
GO
CREATE TABLE [dbo].[Products]
(
            [ProductID] [int] IDENTITY(1,1) NOT NULL Primary Key,
            [ProductName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](max) NOT NULL,
            [ImagePath] [nvarchar](max) NULL,
            [UnitPrice] [float] NULL,
            [CategoryID] [int] NOT NULL,
  --Table level foreign key ,pointing to categories table
            CONSTRAINT [FK_Products_Categories] FOREIGN KEY([CategoryID]) REFERENCES
Categories(CategoryID)
 )
GO

The PRIMARY KEY uniquely identifies each record in a database table; main goal is to prevent duplicate values for columns and provides unique identifier to each column.

1. Primary key creates clustered index on the columns.
2. Primary keys must contain UNIQUE values.
3. A primary key column cannot contain NULL values.

Example:

Column level:

USE [WebShop]
CREATE TABLE [dbo].[Department](
            [DepartmentId] [smallint] IDENTITY(1,1) NOT NULL Primary Key,
            [DeptName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](2000) NULL,
            [CreatedBy] [uniqueidentifier] NOT NULL,
            [CreatedDate] [datetime] NULL,
            [UpdateBy] [nvarchar](256) NULL,
            [UpdateDate] [datetime] NULL
 )

Table Level:

USE [WebShop]
CREATE TABLE [dbo].[Department](
            [DepartmentId] [smallint] IDENTITY(1,1) NOT NULL,
            [DeptName] [nvarchar](100) NOT NULL,
            [Description] [nvarchar](2000) NULL,
            [CreatedBy] [uniqueidentifier] NOT NULL,
            [CreatedDate] [datetime] NULL,
            [UpdateBy] [nvarchar](256) NULL,
            [UpdateDate] [datetime] NULL
            CONSTRAINT PK_Department  PRIMARY KEY(DepartmentId)
 ) 

The UNION ALL clause/operator is used to combine the results of two or more select statements
Including duplicate rows/records.

Syntax:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Example:

SELECT * FROM [UserData]
UNION ALL
SELECT * FROM Employee