Enforcing Integrity Constraints and modifying the properties of tables and fields

🧩 Enforcing Integrity Constraints and Modifying Properties of Tables and Fields in MS Access

Microsoft Access allows you to maintain data accuracy and consistency through integrity constraints and customizable table and field properties. This helps prevent invalid data entry and ensures that your database functions correctly.

🔐 What are Integrity Constraints?

Integrity constraints are rules that ensure the validity of data in a database. In MS Access, the most commonly used constraints are:

• Primary Key: Uniquely identifies each record in a table.
• Foreign Key: A field in one table that refers to the Primary Key in another table.
• Referential Integrity: Ensures that relationships between tables remain consistent.
• Validation Rules: Restrict the values that users can enter into a field.
• Required Fields: Forces data entry in certain fields before a record can be saved.

🧮 Setting Up a Primary Key

1. Open the table in Design View.
2. Right-click on the field you want to set as a Primary Key (e.g., StudentID).
3. Select Set Primary Key.

🔗 Enforcing Referential Integrity (Foreign Key)

1. Go to the Database Tools tab and click on Relationships.
2. Add both related tables.
3. Drag the primary key field from the main table to the corresponding foreign key field in the related table.
4. In the dialog box, check Enforce Referential Integrity.
5. Click Create.

This prevents deletion or modification of a record in the parent table if related records exist in the child table.

⚙️ Modifying Field Properties

You can control how data is entered and displayed by modifying field properties in Design View.

Common Properties:

• Field Size: Controls the maximum number of characters (for text fields).
• Format: Changes how data is displayed (e.g., Date formats).
• Input Mask: Provides a pattern for data entry (e.g., phone numbers).
• Default Value: Automatically enters a value when a new record is created.
• Validation Rule: Limits acceptable values (e.g., Age >= 18).
• Validation Text: Message displayed when a rule is violated.
• Required: Prevents leaving a field blank.

🧪 Example: Adding a Validation Rule

1. Open the table in Design View.
2. Select the Age field.
3. In the Validation Rule property, type: >= 18
4. In Validation Text, type: "Age must be 18 or older"

📌 Best Practices

• Always assign a Primary Key to ensure unique records.
• Use referential integrity to maintain relationships between related tables.
• Use validation rules to prevent invalid data entry.
• Set field properties according to the nature of the data.

🎯 Learning Outcomes

• Understand how to apply data validation and enforce data integrity.
• Modify field properties to control user input and improve data quality.
• Develop good database design practices.

Using these tools, ITI students can build professional-grade databases that are reliable and easy to manage.