List Entity and Attribute Constraints

Constraints are rules imposed upon certain elements in a database to preserve data integrity^[1]. Entity and attribute constraints are rules imposed upon entities and attributes. Several constraints were mentioned in this module, and are summarized below.

The following two constraints were identified for entities:

1. Every entity in a database must have a different name.
2. Every entity must have an entity identifier ( key attribute).

• Attribute constraints
  The following two constraints were identified for attributes:
  1. Every attribute must have a specific domain type ( “domain constraint”).
  2. Every attribute must be single-valued.
  
  As the course progresses, other constraints will be identified and explained..
• Domain Attributes: A very common type of attribute constraint is a set of values that shows the possible values an attribute can have. Such a set is called a domain. Very common domains are, for example:
  
  1. Yesno: Yes, No
  2. Gender: Male, Female, Unknown
  3. Weekday: Sun, Mon, Tue, Wed, Thu, Fri, Sat
  
  In a conceptual data model you can recognize these as entities with (usually) only two attributes:
  Code and Description. These domain entities are referred to frequently but do not have any 'many' relationships of their own . Typically, you would know all the values before the system is built. The number of values is normally low. Often you would deliver such a system with non-empty code tables.
  An alternative model for the (sometimes many) code entities is a more generic, two entity approach:
  1. CODE and
  2. CODE TYPE
  
  The conceptual data model has the advantage of fewer relationships per entity as well as easy to understand entities.
  The two entity approach has obviously fewer entities and therefore will lead to fewer tables.
  Domains that have a large number of values, such as all positive integers up to a particular value, are usually not modeled. You should list and describe such a constraint in a separate document.
  

Constraints in a database are rules that enforce data integrity by ensuring that the data entered into the database remains accurate, consistent, and reliable. They prevent invalid, incomplete, or inconsistent data from being stored, thus preserving the quality of information in the database.
Here’s how different types of constraints help maintain data integrity:

1. Primary Key Constraint (PK) – Ensures Uniqueness and Entity Integrity
   • The Primary Key uniquely identifies each record in a table.
   • It ensures that no two rows have the same key value, preventing duplicate records.
   • It also enforces entity integrity, meaning that every record must be uniquely identifiable.
2. Foreign Key Constraint (FK) – Enforces Referential Integrity
   • A Foreign Key establishes a relationship between two tables, linking a column in one table to a Primary Key in another.
   • It ensures that referenced values exist in the parent table, preventing orphan records and maintaining referential integrity.
   • It enforces cascading operations like ON DELETE CASCADE to maintain consistency when related records are removed.
3. Unique Constraint – Prevents Duplicate Values
   • Ensures that all values in a specific column (or a combination of columns) are distinct.
   • Unlike a Primary Key, a table can have multiple Unique Constraints, and they can allow NULL values (though usually only one per column).
4. Not Null Constraint – Ensures Required Data is Present
   • Prevents a column from having NULL values, ensuring that essential data fields always contain valid information.
5. Check Constraint – Enforces Domain Integrity
   • Defines a condition that values in a column must satisfy.
   • Example: CHECK (age >= 18) ensures only valid age values are inserted.
   • Maintains domain integrity by restricting values to a predefined set.
6. Default Constraint – Provides Default Values
   • Assigns a default value to a column when no explicit value is provided.
   • Ensures consistency in cases where a value is not specified by the user.

How These Constraints Preserve Data Integrity

• Prevention of Duplicate Data: Primary Key and Unique Constraints ensure that records remain unique and avoid redundancy.
• Enforcement of Valid Relationships: Foreign Key Constraints maintain referential integrity, preventing broken relationships in relational databases.
• Data Completeness: Not Null Constraints ensure that mandatory fields are always filled.
• Controlled Data Values: Check Constraints restrict column values to valid ranges or conditions.
• Consistency Across Transactions: Constraints work together to prevent anomalies, ensuring ACID (Atomicity, Consistency, Isolation, Durability) compliance in transactional databases.

The next lesson concludes the module.

[1] Data integrity: A term used to describe the quality (in terms of accuracy, consistency, and validity) of data in a database, in the sense that values required to enforce data relationships actually exist. Problems with data integrity occur when a value in one table that is supposed to relate to a value in another cannot because the second value either has been deleted or was never entered.