Saturday 30 June 2012

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
  1. Domain Integrity
  2. Referential Integrity
  3. 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

 
LinkedIn
 
 
 
JOYDEEP DAS
 
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

 
 
 
 
 
 
You are receiving Invitation to Connect emails. Unsubscribe
© 2012, LinkedIn Corporation. 2029 Stierlin Ct. Mountain View, CA 94043, USA
 

Friday 29 June 2012

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

Thursday 28 June 2012

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

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