Relational Database Constraints

 

Relational Model Constraints and Relational Database Schemas

So far, the discussion of the relational model has focused mainly on the properties of individual relations, such as attributes, tuples, and domains. However, a relational database typically consists of many relations, and these relations are logically connected to represent a real-world application, or miniworld.

At any given point in time, the state of a relational database is determined by the current states of all its relations. Not all combinations of relation states are valid. There are usually restrictions on the values that can be stored in the database. These restrictions are known as constraints and are derived from the rules and semantics of the miniworld being modeled.

---

Meaning of Constraints in a Relational Database

A database constraint is a rule that the data in the database must satisfy at all times. Constraints ensure that the database remains:

• Accurate

• Consistent

• Meaningful

• Representative of real-world rules

Without constraints, a database could store incorrect or contradictory data, reducing its usefulness and reliability.

When we refer to a relational database, we implicitly include both its schema and its current state. A database state that does not obey all the integrity constraints is called not valid, and a state that satisfies all the constraints in the defined set of integrity constraints IC is called a valid state.

---

Classification of Database Constraints

Constraints are classified into three major categories, based on how and where they are specified and enforced.

---

1. Inherent (Implicit) Model-Based Constraints

Definition

These constraints are built into the relational data model itself. They arise from the formal definition of a relation and are therefore automatically enforced by any relational DBMS.

Examples

• A relation is a set of tuples, so duplicate tuples are not allowed

• The order of tuples in a relation is irrelevant

• Each attribute value must be atomic (First Normal Form)

Key Point

These constraints:

• Do not need to be explicitly declared

• Apply to all relational databases

---

2. Schema-Based (Explicit) Constraints

Definition

Schema-based constraints are those that can be directly specified in the relational schema, typically using a Data Definition Language (DDL) such as SQL.

Importance

• Automatically enforced by the DBMS

• Prevent invalid data from being stored

• Provide strong data integrity guarantees

---

Types of Schema-Based Constraints

Schema-based constraints include:

1. Domain constraints

2. Key constraints

3. Constraints on NULL values

4. Entity integrity constraints

5. Referential integrity constraints

---

(a) Domain Constraints

Domain constraints specify the allowable values for each attribute.

Explanation

Each attribute in a relation is associated with a domain, which defines:

• Data type (INTEGER, CHAR, DATE, etc.)

• Range of values

• Format or length

Examples

• Age must be an integer between 0 and 120

• Salary must be a positive number

• Gender ∈ {‘M’, ‘F’}

Role

Domain constraints ensure that individual attribute values are valid and meaningful.

---

(b) Key Constraints

Key constraints ensure uniqueness of tuples.

Superkey

A superkey is a set of attributes that uniquely identifies a tuple.

Formally:
For any two tuples t1 and t2:

t1[SK] ≠ t2[SK]

Example:

• In STUDENT(Name, Ssn, Age)

  • {Ssn} → superkey

  • {Ssn, Name} → superkey

---

Key (Candidate Key)

A key is a minimal superkey.

Properties:

1. Uniqueness – no duplicate values

2. Minimality – removing any attribute breaks uniqueness

Example:

• {Ssn} → key

• {Ssn, Name} → not a key (redundant attributes)

---

Candidate Keys and Primary Key

• A relation may have multiple keys

• Each is a candidate key

• One is chosen as the primary key

• Primary key attributes are underlined

Example (CAR relation):

• Candidate keys:

  • License_number

  • Engine_serial_number

• One is chosen as primary key

Key constraints ensure that tuples in a relation are uniquely identifiable.

Summary:

Superkey

A superkey is a set of one or more attributes that uniquely identifies a tuple.

Candidate Key

A candidate key is a minimal superkey, meaning that no proper subset of it can uniquely identify tuples.

Primary Key

• One candidate key is chosen as the primary key

• Primary key attributes are underlined

• Used as the main means of tuple identification

➡ Key constraints prevent duplicate and ambiguous tuples.

---

(c) Constraints on NULL Values

These constraints specify whether an attribute is allowed to have NULL values.

Explanation

NULL represents:

• Unknown value

• Not applicable value

• Missing value

Attributes may or may not allow NULL values.

• NOT NULL constraint → NULL values not allowed

• Primary key attributes must not be NULL

Examples

• EMPLOYEE.Name → NOT NULL

• EMPLOYEE.Middle_name → NULL allowed

Such constraints help avoid incomplete or ambiguous data.

---

(d) Entity Integrity Constraint

Definition

The entity integrity constraint states that:

No primary key attribute of a relation can be NULL.

Reason

• Primary keys are used to uniquely identify tuples

• A NULL value would make identification impossible

Example

• EMPLOYEE.Ssn cannot be NULL

This constraint applies within a single relation.

---

(e) Referential Integrity Constraint

Definition

Referential integrity constraints ensure that relationships between relations remain consistent.

Foreign Keys

Definition

A foreign key (FK) in relation R1 references the primary key (PK) of relation R2.

Conditions:

1. dom(FK) = dom(PK)

2. FK value must:

   • Match a PK value in R2, or

   • Be NULL

---

Example from COMPANY Database

• EMPLOYEE(Dno) → references DEPARTMENT(Dnumber)

• WORKS_ON(Essn) → references EMPLOYEE(Ssn)

• WORKS_ON(Pno) → references PROJECT(Pnumber)

---

Self-Referencing Foreign Key

A foreign key can reference the same relation.

Example:

• EMPLOYEE.Super_ssn → EMPLOYEE.Ssn

  • Represents supervisor relationship

Explanation

If a relation R1 includes a foreign key that references the primary key of relation R2, then:

• The foreign key value must either:

  • Match an existing primary key value in R2, or

  • Be NULL (if allowed)

Example

EMPLOYEE(Dno) → DEPARTMENT(Dnumber)

This ensures that:

• An employee cannot be assigned to a non-existent department

➡ Referential integrity maintains inter-relation consistency.

---

3. Application-Based (Semantic) Constraints

Definition

These constraints:

• Cannot be directly expressed in the relational schema

• Depend on business rules and application semantics

Examples

• An employee’s salary must not exceed that of their supervisor

• A student must complete prerequisites before enrolling in a course

Enforcement

• Application programs

• SQL assertions

• Database triggers

State Constraints vs Transition Constraints

State Constraints

• Must hold in every database state

• Examples:

  • Referential integrity

  • Entity integrity

Transition Constraints

• Restrict changes between states

• Example:

  • Salary can only increase

---

Data Dependencies

Another important category of constraints mentioned in this section is data dependencies, such as:

• Functional dependencies

• Multivalued dependencies

These constraints are mainly used to:

• Evaluate the quality of database design

• Perform normalization (will study later)

---

Summary

Category	Description
Inherent constraints	Built into relational model
Schema-based constraints	Defined in schema using DDL
Application-based constraints	Enforced through business logic
Domain constraints	Restrict attribute values
Key constraints	Ensure uniqueness
Entity integrity	Primary key not NULL
Referential integrity	Maintain consistency across relations