View is a virtual table based on the result-set of an SQL statement and that is Stored in the database with some name.

Syntax:

CREATE VIEW SomeViewName AS
SELECT Some_columnName(s)
FROM tableName
WHERE condition 

Example:

Create View:

CREATE VIEW StudentProducts AS
 
SELECT  * FROM Products WHERE CategoryId in (Select CategoryID from Categories where CategoryName='Books')
 
GO  

Update View:

MS-SQL supported

ALTER VIEW StudentProducts AS
 
SELECT  * FROM Products WHERE CategoryId in (Select CategoryID from Categories where CategoryName in ('Books','Mobile'))
 
GO

Oracle supported:

CREATE OR REPLACE VIEW StudentProducts AS
 
SELECT  * FROM Products WHERE CategoryId in (Select CategoryID from Categories where CategoryName in ('Books','Mobile'))
 
GO

DROP view:

DROP View StudentProducts 

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)
 )