Relational Data Modeling - (Integrity) (Constraints|action assertions)

Data System Architecture

About

Logical Data Modeling - Constraint

Constraints are a set of rule inside a relational database that declare consistency rules in order to:

Every enterprise constrains behavior in some way, and this is closely related to constraints on what data may or may not be updated. To prevent a record from being made is, in many cases, to prevent an action from taking place.

But even at one-third the size, nine thousand lines, being able to recite the poem is impressive: at one second per line, the verses would take two and one-half hours to recite. It is impressive even allowing for the fact that the poem is re-created as opposed to memorized, because neither the singer nor the audience expect word-for-word accuracy (nor would either have any way of verifying that). Most of us do not learn epic poems. But we do make use of strong constraints that serve to simplify what must be retained in memory.

Type

Three basic types of database integrity constraints are:

  • Entity integrity, allowing no two rows to have the same identity within a table.
  • Domain integrity, restricting data to predefined data types, e.g.: dates.
  • Referential integrity, requiring the existence of a related row in another table, e.g. a customer for a given customer ID.

Any applicable integrity constraints and data validation rules must be satisfied before permitting a change to the database.

Purpose

Constraints are used for these purposes:

  • Data Quality

Constraints verify that the data conforms to a basic level of data consistency and correctness, preventing the introduction of dirty data. They are the key part to ensure referential integrity.

  • Query optimization

The Database utilizes constraints when optimizing SQL queries. Although constraints can be useful in many aspects of query optimization, constraints are particularly important for query rewrite. (for instance on materialized views. See query rewriting)

List





Recommended Pages
Card Puncher Data Processing
Business - Rule

A business rule is a rule that constrains some aspect of the business process. According to the white paper by the Business Rules Group, a business...
Data System Architecture
Data Property - ACID (atomicity, consistency, isolation, durability)

In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee transactions are processed reliably. They defines a concurrency model. Traditional database...
Data System Architecture
Logical Data Modeling - Data Integrity

Data/Database integrity ensures that data entered into the database is: accurate, correct (valid), and consistent. Three basic types of integrity are: Entity integrity, allowing no two rows...
Card Puncher Data Processing
Oracle Database - (Integrity) Constraints

Constraints on tables. Violation of constraint is not a syntax error but a run-time error. See A UNIQUE key integrity constraint requires that every value in a column or set of columns (key)...
Relational Data Model
RDBMS - Relational Model

The relational model for database management: is a set of principles for relational databases formalized by Dr. E.F. Codd in the late 1960s. is a database model based on first-order predicate logic...
Data System Architecture
Relational Data Modeling

This section is data Modeling applied to a relational structure (mostly table). Relational databases normalize data, removing as much duplication as possible. A relational database’s approach to...
Data System Architecture
Relational Data Modeling - Foreign Key Constraint (one-to-many relationship)

A Foreign Key Constraint is a constraint that creates a one to many relationship between a primary key and a foreign key (ie between two tables) The foreign key constraint on a table A will : create...
Data System Architecture
Relational Data Modeling - Primary Key Constraint

A primary key constraint is a constraint for a primary_key. It's also the declaration/creation statement of a primary key.
Data System Architecture
Relational Data Modeling - Unique (Constraint|Key)

Unique key implementation in a relational environment A unique constraint is a constraint that enforces the Entity Integrity e.g. that every value in a column or set of columns (key) be unique—that...
Data System Architecture
SQL - Data Manipulation Language (DML)

Data Manipulation Language (DML) is a category of SQL statement that modify the data of a database. The principal commands are: SELECT UPDATE, INSERT, and DELETE but you can also find: ...



Share this page:
Follow us:
Task Runner