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

Invitation to connect on LinkedIn

From JOYDEEP DAS Sr. Software Engineer at Gamut Infosystems Limited Kolkata Area, India I'd like to add you to my professional network on LinkedIn. - JOYDEEP Confirm that you know JOYDEEP You are receiving Invitation to Connect emails. Unsubscribe © 2012, LinkedIn Corporation. 2029 Stierlin Ct. Mountain View, CA 94043, USA

COMMIT and ROLLBACK TRANSACTION in Triggers

One of my friends asks me to write an article related to COMMIT and ROLLBACK TRANSACTION in Triggers.

After reading one of my previous article

DELETE restriction of VIEWS

http://sqlknowledgebank.blogspot.in/2012/06/delete-restrictions-of-views.html

Where I am using a ROLLBACK TRAN in a trigger (INSTEAD OF DELETE trigger), he had a question on mid that without using the BEGIN TRANSACTION on the Trigger, can we us the ROLLBACK TRANSACTION. It is better, if you read my previous article to understand the complete scenario.

The executions of COMMIT TRANSACTION and ROLLBACK TRANSACTION inside the trigger are possible.

When a trigger executes, an implicit transaction is started. If the trigger completes execution and @@TRANCOUNT = 0, error 3609 occurs and the batch is terminated.

If a BEGIN TRANSACTION statement is issued in a trigger, it creates a nested transaction. In this situation, when a COMMIT TRANSACTION statement is executed, the statement will apply only to the nested transaction.

However, in the case of TRIGGER, if BEGIN TRANSACTION is executed before or after the COMMIT TRANSACTION, the error does not occur.

In another scenario of TRIGGER, if BEGIN TRANSACTION is executed after the ROLLBACK TRANSACTION, the error does not occur.

Now I am given an example to illustrate my points. Here I am taking my previous article example to demonstrate it.

IF OBJECT_ID('emp_data') IS NOT NULL

   BEGIN

     DROP TABLE emp_data

   END

GO

CREATE TABLE emp_data

       (

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

         EMPNAME  VARCHAR(50) NOT NULL,

         EMPGRADE VARCHAR(1)  NOT NULL,

         CONSTRAINT CHK_emp_data_EMPGRADE

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

        )

      

GO  

-- Insert records

INSERT INTO emp_data

       (EMPNAME, EMPGRADE)

VALUES ('Sudip Das', 'A'),

       ('Joydeep Das', 'B'),

       ('Sangram Jit', 'B'),

       ('Tuhin Shinah', 'B'),

       ('Bhola', 'C')   

     

-- Display records

SELECT IDNO, EMPNAME, EMPGRADE

FROM   emp_data      

    

-- Output

IDNO  EMPNAME           EMPGRADE

1     Sudip Das         A

2     Joydeep Das       B

3     Sangram Jit       B

4     Tuhin Shinah      B

5     Bhola             C

   

-- Creation of VIEWS

IF OBJECT_ID('view_emp_data') IS NOT NULL

   BEGIN

     DROP VIEW  view_emp_data

   END

GO

CREATE VIEW  view_emp_data

AS

SELECT IDNO, EMPNAME, EMPGRADE

FROM   emp_data

GO

-- Display the Records of Views

SELECT IDNO, EMPNAME, EMPGRADE

FROM   view_emp_data

-- Output from View

IDNO  EMPNAME           EMPGRADE

1     Sudip Das         A

2     Joydeep Das       B

3     Sangram Jit       B

4     Tuhin Shinah      B

5     Bhola             C

Now I am creating the trigger with simple ROLLBACK TRANSACTION without any BEGIN TRANSACTION.        

IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL

   BEGIN

     DROP TRIGGER trg_RestrictDEL

   END

GO

      

CREATE TRIGGER trg_RestrictDEL ON view_emp_data

INSTEAD OF DELETE

AS

  BEGIN

       IF @@rowcount > 0

       BEGIN

          RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16, 2 )

          ROLLBACK TRANSACTION

       END

      

END

GO

Now I am trying to DELETE records

DELETE view_emp_data

WHERE IDNO=3

Please Look at the Error Message. The Error portion is highlighted here.

Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 10

Rows from Views Named: view_emp_data, CANNOT be DELETED!

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

Now I am trying to ALTER this Trigger with BIGIN TRANSACTION, immediate after the ROLLBACK TRANSACTION. We find no Error after execution. (Please try this)

IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL

   BEGIN

     DROP TRIGGER trg_RestrictDEL

   END

GO

      

CREATE TRIGGER trg_RestrictDEL ON view_emp_data

INSTEAD OF DELETE

AS

  BEGIN

       IF @@rowcount > 0

       BEGIN

                 BEGIN TRAN

                 RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16, 2 )

                 ROLLBACK TRANSACTION

                 BEGIN TRANSACTION

       END

      

END

GO

Now execute the DELETE statement again.

DELETE view_emp_data

WHERE IDNO=3

Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 10

Rows from Views Named: view_emp_data, CANNOT be DELETED!

No error message.

Hope you like it.

Posted by: MR. JOYDEEP DAS

DELETE restrictions of VIEWS

One of my friends told me that, he has a VIEW from a single base table and the user of the view is always trying to DELETE the records from VIEW. So the underlying TABLE records are also deleted for deletion actions of the view. He wants to restrict the deletions of records from VIEW.

The article is related to the DELETE restrictions of VIEWS. We can easily do it by REVOKING the DELETE permissions from the VIEW objects. But my friends have no permission over GRANT and REVOKE.

So the solution is given by using the INSTEAD OF DELETE Trigger.  Here I am not going to describe the definition of it.

To know more about it, just go to my previous article.

FOR | AFTER | INSTEAD OF

http://sqlknowledgebank.blogspot.in/2012/03/for-after-instead-of.html

Rather I just provide a simple self explanatory example to demonstrate it.

-- Base Table

IF OBJECT_ID('emp_data') IS NOT NULL

   BEGIN

     DROP TABLE emp_data

   END

GO

 

CREATE TABLE emp_data

       (

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

         EMPNAME  VARCHAR(50) NOT NULL,

         EMPGRADE VARCHAR(1)  NOT NULL,

         CONSTRAINT CHK_emp_data_EMPGRADE 

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

        )

       

GO   

-- Insert records

INSERT INTO emp_data 

       (EMPNAME, EMPGRADE)

VALUES ('Sudip Das', 'A'),

       ('Joydeep Das', 'B'),

       ('Sangram Jit', 'B'),

       ('Tuhin Shinah', 'B'),

       ('Bhola', 'C')    

      

-- Display records

SELECT IDNO, EMPNAME, EMPGRADE 

FROM   emp_data       

     

-- Output

IDNO  EMPNAME           EMPGRADE

1     Sudip Das         A

2     Joydeep Das       B

3     Sangram Jit       B

4     Tuhin Shinah      B

5     Bhola             C

    

-- Creation of VIEWS

IF OBJECT_ID('view_emp_data') IS NOT NULL

   BEGIN

     DROP  VIEW   view_emp_data

   END

GO

CREATE VIEW  view_emp_data

AS

SELECT IDNO, EMPNAME, EMPGRADE 

FROM   emp_data

GO 

-- Display the Records of Views

SELECT IDNO, EMPNAME, EMPGRADE 

FROM   view_emp_data

-- Output from View

IDNO  EMPNAME           EMPGRADE

1     Sudip Das         A

2     Joydeep Das       B

3     Sangram Jit       B

4     Tuhin Shinah      B

5     Bhola             C

-- Protect DELETION From VIEWS

IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL

   BEGIN

     DROP  TRIGGER  trg_RestrictDEL 

   END

GO

       

CREATE TRIGGER trg_RestrictDEL ON view_emp_data

INSTEAD OF DELETE

AS

  BEGIN

       IF @@rowcount > 0

       BEGIN

       RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16, 2 )

       ROLLBACK

  END

END

GO

-- Now try to delete records from views

DELETE view_emp_data

WHERE IDNO=1

-- Output generated for DELETE Action

Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 8

Rows from Views Named: view_emp_data, CANNOT be DELETED!

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

Hope you like it.

Posted by: MR. JOYDEEP DAS