Data/ Entity/ Domain/ Referential/ User-defined integrity
We all talk about the Data, Entity, Domain, Referential andUser-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
- Domain Integrity
- Referential Integrity
- 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.
SQL Table Constraints
Some of my readers mailed me that,
"Why you write simple article in your web. As you your web contains very good stuff… but some tropics are really simple."
It is facts. I have long list of friends from Linked In web. They send me regular mail to write different types of tropics. From them there is a huge number of junior professional and students. I have to serve them all. Students are a very important reader in my article and I think they really need guideline and I can't ignore them. I also take free class for them in weekends and try to share my knowledge with them. I really like it….
In this article I am trying to illustrate some points related to table constraints. It's not only useful for junior professional but also useful for all readers. So let's starts.
Data integrity rules falls into three categories:
1. Entity
2. Referential
3. Domain
Entity integrity
Entity integrity ensures that the each row of the database is uniquely identified. You can specify the PRIMARY KEY constraint in the table.
Referential Integrity
It ensure that the relationship between tables remain preserved as data is INSERTED, DELETED and MODIFIED.
We can ensure the referential integrity by FOREIGN KEY constraints.
Take an example of "Person" and "Orders" table:
The "Persons" table:
P_Id
|
LastName
|
FirstName
|
Address
|
City
| |||||
1
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
| |||||
2
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
| |||||
3
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
The "Orders" table:
O_Id
|
OrderNo
|
P_Id
| |||
1
|
77895
|
3
| |||
2
|
44678
|
3
| |||
3
|
22456
|
2
| |||
4
|
24562
|
1
|
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
SQL Statements
CREATETABLE Person
( P_id int NOTNULL PRIMARYKEY,
LastName Varchar(50) NOTNULL,
FirstName Varchar(50) NOTNULL,
Address Varchar(100) NULL,
City Varchar(50) NULL
)
GO
CREATETABLE Orders
( O_Id int NOTNULLPRIMARYKEY,
OrderNo int NOTNULL,
P_Id int FOREIGNKEY REFERENCES Persons(P_Id)
)
If the table objects are already created we can use the Alter statements to set the foreign key.
ALTERTABLE Orders
ADDCONSTRAINT fk_PerOrders
FOREIGNKEY(P_Id)REFERENCES Persons(P_Id)
Also we can use CASCADE DELETE with referential integrity. In the example if we delete a specified person from the person table the corresponding Order will be automatically deleted.
Example:
ALTERTABLE Orders
ADDCONSTRAINT fk_PerOrders
FOREIGNKEY(P_Id)
REFERENCES Persons(P_Id)ONDELETECASCADE
We can also use CASCADE UPDATE with referential integrity. CASCADE specifies that the column will be updated when the referenced column is updated.
ALTERTABLE Orders
ADDCONSTRAINT fk_PerOrders
FOREIGNKEY(P_Id)
REFERENCES Persons(P_Id)ONUPDATECASCADE
You can check if any foreign key is defined in your database with cascading actions usingsys.foreign_keys
SELECT name AS [Constraint Name],
OBJECT_NAME(referenced_object_id) [Referenced Object],
OBJECT_NAME(parent_object_id) [Parent Object],
delete_referential_action_desc [ON DELETE],
update_referential_action_desc [ON UPDATE]
FROM sys.foreign_keys
Domain Integrity
Domain integrity ensures that the values inside a database follow defined rules of values, range and format. Database can enforce the rules using
1. CHECK Constraints
2. UNIQUE Constraints
3. DEFAULT Constraints
Check Constraints
Check constraints contain an expression the database will evaluate when we modify or insert a row. If the expression evaluates to false, the database will not save the row. Building a check constraint is similar to building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. You can also build expressions around AND and OR operators. We can use check constraints to implement business rules, and tighten down the allowed values and formats allowed for a particular column.
Example:
CREATETABLE Products_2
(
ProductID int PRIMARYKEY,
UnitPrice money CHECK(UnitPrice > 0 AND UnitPrice < 100)
)
OR
ALTERTABLE Employees_2
ADD CONSTRAINT CK_HireDate CHECK(hiredate <GETDATE())
The UNIQUE Constraints
The unique constraints determine that the columns values must be unique. Only one NULL is allowed by UNIQUE constraints.
Example:
CREATETABLE Products_2
(
ProductID int PRIMARYKEY,
ProductName nvarchar(40) Constraint IX_ProductName UNIQUE
)
OR
ALTERTABLE Products_2
ADD CONSTRAINT IX_ProductName UNIQUE(ProductName)
Default Constraints
Default constraints apply a value to a column when an INSERT statement does not specify the value for the column. Although default constraints do not enforce a rule like the other constraints we have seen, they do provide the proper values to keep domain integrity intact. A default can assign a constant value, the value of a system function, or NULL to a column. You can use a default on any column except IDENTITY columns and columns of type timestamp.
Example:
CREATETABLE Orders_2
(
OrderID int IDENTITY NOTNULL,
EmployeeID int NOTNULL,
OrderDate datetime NULL DEFAULT(GETDATE()),
Freight money NULL DEFAULT(0)CHECK(Freight >= 0),
ShipAddress nvarchar(60) NULL DEFAULT('NO SHIPPING ADDRESS'),
EnteredBy nvarchar(60) NOTNULL DEFAULT(SUSER_SNAME())
)
Constraint Maintenance
After creating the constraint, if you think that the you don't needed the constraints you can drop the constraints or disable/enable the constraints.
To DROP the constraints
ALTERTABLE Products
DROPCONSTRAINT CK_Products_UnitPrice
Disable/Enable Constraints
ALTERTABLE Products NOCHECKCONSTRAINT CK_UnitsOnOrder
ALTERTABLE Products NOCHECKCONSTRAINT ALL
ALTERTABLE Products CHECKCONSTRAINT CK_UnitsOnOrder
ALTERTABLE Products CHECKCONSTRAINT ALL
Hope you like it.
