For an instructor lead, in-depth look at learning SQL click below.
Data Governance is a critical aspect of managing and controlling access to your data. In SQL Server, this is no different. Ensuring the accuracy and consistency of data is a crucial part of data governance. Here, we are going to explore how to guarantee this aspect using SQL Server.
What is Data Integrity?
Data Integrity refers to maintaining and assuring the accuracy and consistency of data over its entire lifecycle. It means the data in your database remains unaltered when it is not in a state of modification and data consistency is preserved when it is modified.
Data Integrity Types
The accuracy and consistency of data can be ensured at various levels. Here, they are categorized into three types:
1. Entity Integrity
Safeguarding against duplicate rows from participating in the relation. Constraints such as the PRIMARY KEY constraint can be used to enforce entity integrity. For example:
|
1 2 3 4 5 6 7 |
CREATE TABLE SalesOrderHeader ( SalesOrderID INT NOT NULL PRIMARY KEY, CustomerID INT NOT NULL, OrderAmount money NOT NULL ); |
2. Domain Integrity
This refers to inserting values in the columns which should fit in the provided domain. For example, if the ‘OrderAmount’ is defined as ‘MONEY’, then any text string should not be a valid value. Let’s see online verification of domain integrity:
|
1 2 3 4 5 6 7 |
CREATE TABLE SalesOrderHeader ( SalesOrderID INT NOT NULL PRIMARY KEY, CustomerID INT NOT NULL, OrderAmount money CHECK (OrderAmount > 0) NOT NULL ); |
3. Referential Integrity
Assuring that relationships between tables remain consistent. This is usually enforced with foreign keys. For example:
|
1 2 3 4 5 6 7 |
CREATE TABLE SalesOrderDetail ( SalesOrderID INT NOT NULL FOREIGN KEY REFERENCES SalesOrderHeader(SalesOrderID), OrderDetailID INT NOT NULL, ProductID INT NOT NULL ); |
Conclusion
Data governance in SQL Server helps you maintain the level of data quality that meets the regulatory compliance level and also supports business decisions. Understanding how SQL Server handles these operations can help in designing databases that deliver correct results.