Integrity ensures the accuracy and reliability of the data in
the database.
·
Entity integrity: No duplicate records/rows
in a table (PRIMARY KEY).
· Domain integrity: It enforces valid
entries for a given column by restricting the type, the format, or the range of
values (CHECK, DEFAULT, NOT NULL).
· Referential integrity: Records/Rows not to
be deleted, which are used by other records (FOREIGN KEY).
· User-defined
integrity:
It enforces some specific business rules that are defined by users. These rules
are unlike from entity, domain or referential integrity.
Example For User-defined integrity
--------------------------------------------
CREATE FUNCTION
dbo.USP_TT_Employee_Gender_Check
(
@Gender char(1)
)
RETURNS tinyint
AS
BEGIN
DECLARE @Result tinyint
= 0
IF EXISTS(SELECT 1 FROM
Employee
WHERE Gender=@Gender)
SET @Result= 1
RETURN @Result
END
-------------------------------------------
CREATE TABLE
[dbo].[Employee_Check](
[EmployeeID] int NOT NULL IDENTITY(1,1),
[EmployeeName] varchar(50) NOT NULL,
[Gender] char(1) NOT NULL,
CONSTRAINT ChkNoSameNameandGender CHECK (dbo.USP_TT_Employee_Gender_Check([Gender]) = 1),
CONSTRAINT [PK_Employee_Check_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)
)
-------------------------------------------
