- August 18, 2017
- Posted by: marlenedubois
- Category: CPR Training
Enforcing data integrity ensures the quality of data in a database. For example, if a product can be entered having a Product_ID value of 25 in a table named Products, the database should not allow another product to have an ID with the same value. Furthermore, if there can be a column named Product_Rating which can be intended to have values ranging via 1 to 10, the database should not accept a value below 1 in addition to also above 10 with This particular column. This particular can be accomplished by using the methods supported by SQL Server to enforce the integrity of the data.
SQL Server supports quite a few methods which can be used to enforce data integrity. These methods include defining datatypes, NOT NULL definitions, DEFAULT definitions, IDENTITY properties, rules, constraints, triggers, in addition to also indexes.
A datatype can be an attribute which specifies the type of data (e.g., character, integer, binary, etc.) which can be stored in a column, parameter, or variable. SQL Server provides a set of system-supplied datatypes. However, users can also create user-defined datatypes based on the system-supplied datatypes. System-supplied datatypes define all of the types of data which can be used with SQL Server. Datatypes can be used to enforce data integrity because the data entered or modified must conform to the type specified for the object. For example, a name cannot be stored in a column defined with the datetime datatype, as a datetime column can accept only date values.
NOT NULL Definitions
The nullability of a table column determines whether the rows from the table can contain a null value for which column. A null value in a column does not mean which the column has zero, blank, or a zero-length character string such as ” “. Null in a column means which no data has been entered in which column. The presence of a null in a column implies which the value can be either unknown or undefined.
The nullability of a column can be defined while defining the column or while creating or modifying a table. The NULL keyword can be used to specify which the column will allow null values. The NOT NULL keyword specifies which null values will not be allowed from the column.
Each column in a row must contain a value even if which value can be null. However, certain situations exist when a row can be inserted in a table, nevertheless the value for a column can be not known or the value does not yet exist. If the column allows null values, a row having a null value for which column can be inserted from the table. In some cases, nullable columns might not be desirable. In these cases, a DEFAULT definition can be defined for the column. Defaults specify what values are automatically inserted in a column if a value can be not specified for the column when inserting a row from the table. For example, the item can be common to specify zero as the default for numeric columns in addition to also N/A as the default for string columns.
When a row can be inserted in a table having a default definition for a column, the SQL Server can be implicitly instructed to insert the specified default value from the column if a value can be not specified for the column.
The IDENTITY property can be used to define a column as an identifier column. An identifier column contains system-generated sequential values which uniquely identify each row from the table. A table can have only one identifier column. Identifier columns usually contain values which are unique only within the table for which they have been defined. In different words, different tables containing identifier columns can contain the same identity values used by another table. However, the identifier values are typically used only within the context of 1 table, in addition to also the identifier columns do not relate to different identifier columns in different tables.
Constraints are used to define the way which SQL Server automatically enforces the integrity of a database. A constraint can be a property assigned to a table or column within a table which prevents invalid data values via being entered from the specified column(s). For example, a PRIMARY KEY or UNIQUE constraint on a column prevents a duplicate value via being inserted into the column. A CHECK constraint on a column prevents the column via accepting a value which does not meet the specified condition. Moreover, a FOREIGN KEY constraint establishes a link between data in two tables.
Rules perform some of the same functions as CHECK constraints. However, CHECK constraints are preferred over rules. Rules are provided only for backward compatibility. CHECK constraints are more concise than rules. A column can have only one rule applied to the item. However, multiple CHECK constraints can be applied to a column. CHECK constraints are specified while creating a table, whereas rules are created as separate objects in addition to also are bound to the column.
The CREATE RULE statement can be used to create a rule. Once a rule has been created, the item can be bound to a column or a user-defined data type by using the sp_bindrule system stored procedure.
Triggers are special types of stored procedures which are defined to execute automatically when an UPDATE, INSERT, or DELETE statement can be issued against a table or view. Triggers can be used to enforce business rules automatically when data can be modified. Triggers can also be used to extend the integrity checking logic of constraints, defaults, in addition to also rules. However, the item can be recommended which constraints in addition to also defaults be used instead of triggers whenever they provide all of the needed functionality.
An index can be a database object which orders the values of one or more columns in a table. An index provides pointers to the data values stored in specified columns of the table in addition to also orders the pointers from the specified order. When rows are requested via an indexed table, the database searches the index to find a particular value in addition to also then follows the pointer to the row containing which value.
Types of Data Integrity
SQL Server supports the following four types of data integrity:
Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).
Domain integrity validates the entries for a given column. Domain integrity can be enforced by restricting the type (through data types), the format (through CHECK constraints in addition to also rules), or the range of possible values (through FOREIGN KEY in addition to also CHECK constraints, DEFAULT definitions, NOT NULL definitions, in addition to also rules).
Referential integrity maintains the defined relationship between tables when records are entered or deleted via the tables. In SQL Server 2000, referential integrity can be based on relationships between foreign keys in addition to also primary keys or between foreign keys in addition to also unique keys (through FOREIGN KEY in addition to also CHECK constraints). Referential integrity ensures which key values are consistent across the related tables.When referential integrity can be enforced, SQL Server prevents users via adding records to a related table if there can be no associated record from the primary table. Users are also prevented via changing values in a primary table or deleting records via the primary table if there are related records from the related table.
User-defined integrity can be used to define specific business rules which do not fall into any of the different integrity categories. All of the integrity categories support user-defined integrity. All column-level in addition to also table-level constraints defined in CREATE TABLE, stored procedures, in addition to also triggers are examples of user-defined integrity.