DBMS ~ Data Integrity & Constraints - rohit120582sharma/Documentation GitHub Wiki

Database Constraints are declarative integrity rules of defining table structures. They include the following 7 constraint types:

• This defines the type of data, data length, and a few other attributes which are specifically associated with the type of data in a column. The column does not accept the values of any other data type.
• Domain constraints are user-defined data type and we can define them like this: Domain Constraint = data type + Constraints (NOT NULL / UNIQUE / PRIMARY KEY / FOREIGN KEY / CHECK / DEFAULT)

• This provides a default value to a column when there is no value provided while inserting a record into a table.

• This makes sure that a column does not hold NULL value. When we don’t provide value for a particular column while inserting a record into a table, it takes NULL value by default.

• This defines a validation rule for the data values in a column so it is a user-defined data integrity constraint. This rule is defined by the user when designing the column in a table.

• This is the unique identifier of the table. Each row must have a distinct value. The primary key can be either a sequentially incremented integer number or a natural selection of data that represents what is happening in the real world (e.g. Social Security Number). NULL values are not allowed in primary key values.

• This defines how referential integrity is enforced between two tables.

• This defines that the values in a column must be unique and no duplicates should be stored.
• Sometimes the data in a column must be unique even though the column does not act as Primary Key of the table. For example CategoryName column is unique in the categories table but CategoryName is not the primary key of the table.

---

Data integrity refers to the accuracy, consistency, and reliability of data that is stored in the database. Data integrity is enforced by database constraints.

The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table. The major types of integrity constraints are:

• Enforced by Domain constraint.

• Enforced by PRIMARY KEY, UNIQUE constraints.

• It is different from domain constraint as here it checks for the validity of the data being entered- like correct age is being entered. Enforced by Domain, DEFAULT, NOT NULL, CHECK, FOREIGN KEY constraints.

• A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).
• The rule states that if there is a foreign key in one relation, either each foreign key value must match a primary key value in the other table or else the foreign key value must be null.
• Referential integrity is defined at the database design time and enforced by FOREIGN KEY constraint when creating table relationships between tables. It will raise errors if the rules are violated.
• Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:
  • Restrict: Disallows the update or deletion of referenced data.
  • Set to Null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
  • Set to Default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
  • Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
  • No Action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle uses No Action as its default action.)

• It is enforced by CHECK constraint.

---