Integrity

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

  )

)

-------------------------------------------