Activity 14: Database Constraint

Database Constraints: Ensuring Data Integrity

Database constraints are rules applied to the structure of a database to maintain the accuracy, consistency, and integrity of the data. These rules control how data is inserted, updated, or deleted, ensuring that the data adheres to business logic and prevents invalid data entry. Constraints also help preserve relationships between tables and enforce data rules in the database.

---

1. Primary Key Constraint

A Primary Key is a unique identifier for each record in a table. It ensures that every record in the table has a unique value and that no record is left without an identifier. A primary key cannot contain NULL values, and a table can have only one primary key, which may consist of one or more columns.

How It Ensures Data Integrity:

• Guarantees that each record has a unique identifier.

• Prevents duplication and ensures that every record is identifiable.

Example Scenario:

In an e-commerce application, the Customers table would use a customer_id as the primary key. This ensures that each customer has a unique identifier.

SQL Example:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL
);

2. Foreign Key Constraint

A Foreign Key is a field (or a group of fields) in one table that links to the Primary Key in another table. It ensures that the values in the foreign key column match valid records in the related table, maintaining referential integrity.

How It Ensures Data Integrity:

• Enforces relationships between tables by making sure that the foreign key references valid records.

• Prevents data inconsistencies, such as "orphaned" records that don't relate to any existing entries in other tables.

Example Scenario:

In the same online store, the Orders table could have a customer_id column that links to the customer_id in the Customers table. This ensures that each order is associated with a valid customer.

SQL Example:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

3. Unique Constraint

A Unique Constraint ensures that all values in a specified column are distinct. Unlike the primary key, you can have multiple unique constraints in a table. Additionally, columns with a unique constraint can contain NULL values, but typically only one NULL is allowed per column.

How It Ensures Data Integrity:

• Prevents duplicate values from being entered into a column that requires uniqueness.

Example Scenario:

In the Customers table, you may want to ensure that each customer has a unique email address to prevent two customers from using the same email.

SQL Example:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    customer_name VARCHAR(100) NOT NULL
);

---

4. Check Constraint

A Check Constraint ensures that the values entered into a column satisfy a specific condition. This is useful for enforcing business rules, such as ensuring that data falls within a certain range or meets certain criteria.

How It Ensures Data Integrity:

• Validates the data before it’s entered into the table, ensuring that it complies with specific rules.

Example Scenario:

In an Orders table, you may want to ensure that the quantity of items in each order is always greater than zero.

SQL Example:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_quantity INT CHECK (order_quantity > 0)
);

---

5. Not Null Constraint

The Not Null Constraint ensures that a column cannot have NULL values. It is used when certain columns must always have a value, ensuring that critical data is not omitted.

How It Ensures Data Integrity:

• Prevents blank fields for critical columns, ensuring that essential information is always present.

Example Scenario:

In the Customers table, the customer_name column would need a NOT NULL constraint because every customer must have a name.

SQL Example:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

---

6. Default Constraint

A Default Constraint automatically assigns a default value to a column if no value is specified during data entry. This ensures that a column always has a value, even if it is not explicitly provided.

How It Ensures Data Integrity:

• Prevents NULL values from being entered when no explicit value is provided.

• Guarantees that certain fields always have a default value when records are created.

Example Scenario:

In the Orders table, if the order_status is not provided during an insert operation, a default value of "Pending" can be assigned automatically.

SQL Example:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_status VARCHAR(50) DEFAULT 'Pending'
);

How Constraints Protect Data Integrity

Database constraints are essential for maintaining the integrity of data in a relational database. By applying these rules, you ensure that:

• Uniqueness and non-nullability are enforced in critical fields (e.g., Primary Key, Unique, Not Null).

• Referential integrity is maintained between tables through Foreign Key constraints.

• Invalid or inconsistent data is prevented by Check constraints.

• Default values are used to avoid unintended NULL entries in specific fields.

These constraints work together to preserve the consistency, accuracy, and correctness of the data within the database.

---

Example Scenarios for Database Constraints

• Primary Key: A student_id in a Students table guarantees that each student has a unique identifier.

• Foreign Key: A teacher_id in a Classes table references the Teachers table, ensuring each class is linked to a valid teacher.

• Unique: An email column in a Users table ensures that no two users share the same email address.

• Check: A price column in a Products table has a CHECK constraint to ensure the price is always positive (e.g., price > 0).

• Not Null: A phone_number field in a Contacts table must contain a value, ensuring that each contact has a valid phone number.

• Default: A status field in a Users table might have a DEFAULT value of active, so every new user is active by default unless stated otherwise.

By defining these constraints, the database can enforce business rules, ensure valid data entry, and maintain data consistency, helping to avoid errors and improve the reliability of the system.