Data/ Entity/ Domain/ Referential/ User-defined integrity

We all talk about the Data, Entity, Domain, Referential and User-defined integrity of the database. I think that we have a clear vision and idea related to those before implementing.

I know that all the developer has the clear vision of that.

This article is dedicated to all of my readers and especially the students who needs to learn or take a quick review about it.

Data Integrity

Data is stored in the columns of the table objects. Data Integrity validates the data before getting stored in the columns of the table. 

For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company.

Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. SQL Server supports four type of data integrity.

     1.     Entity Integrity

2. Domain Integrity
3. Referential Integrity
4. User-defined Integrity

Entity Integrity

Entity integrity ensures each row in a table is a uniquely identifiable entity. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint. For example, the ProductID column of the Products table is a primary key for the table.

Entity Integrity can be enforced through indexes, UNIQUE constraints and PRIMARY KEY constraints. 

Example

-- Primary Key Constraint

IF OBJECT_ID('emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE emp_Dtls

   END

GO

  

CREATE TABLE emp_Dtls

       (empID   INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EmpName VARCHAR(50) NOT NULL)

-- Unique Key Constraint

IF OBJECT_ID('emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE emp_Dtls

   END

GO

  

CREATE TABLE emp_Dtls

       (empID   INT NOT NULL IDENTITY(1,1) UNIQUE,

        EmpName VARCHAR(50) NOT NULL)

Domain Integrity

Domain integrity ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including FOREIGN KEY constraints, CHECK constraints, UNIQUE constraints, and DEFAULT constraints.

Example

-- CHECK Constraint

IF OBJECT_ID('emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE emp_Dtls

   END

GO

  

CREATE TABLE emp_Dtls

       (empID    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EmpGarde VARCHAR(1) NOT NULL,

        CHECK (EmpGarde IN('A', 'B', 'C')))

-- FOREIGN KEY Constraint

IF OBJECT_ID('emp_Dtls') IS NOT NULL

   BEGIN

      DROP TABLE emp_Dtls

   END

GO

IF OBJECT_ID('emp_Master') IS NOT NULL

   BEGIN

      DROP TABLE emp_Master

   END

GO

CREATE TABLE emp_Master

       (empID    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

        EmpName  VARCHAR(50) NOT NULL DEFAULT(''))

       

GO

  

CREATE TABLE emp_Dtls

       (empID    INT NOT NULL UNIQUE,

        EmpGarde VARCHAR(1) NOT NULL,

        FOREIGN KEY (empID) REFERENCES emp_Master(empID))

GO

Referential Integrity

Referential integrity ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. We can apply referential integrity using a FOREIGN KEY constraint and CHECK constraints.

The ProductID column of the Order Details table has a foreign key constraint applied referencing the Orders table. The constraint prevents an Order Detail record from using a ProductID that does not exist in the database. Also, we cannot remove a row from the Products table if an order detail references the ProductID of the row.

User-defined Integrity

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

Hope you like it.

Posted by: MR. JOYDEEP DAS