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