Specifying Constraints in SQL

 

Specifying Constraints in SQL

In a relational database, constraints are rules that restrict the values that can be stored in the database. These rules ensure the correctness, consistency, and integrity of the data. SQL provides several mechanisms to specify constraints directly as part of table definitions.

The constraints discussed in this section are schema-based constraints, meaning they are declared using SQL’s Data Definition Language (DDL) and are enforced automatically by the DBMS.

The main types of constraints covered here are:

  1. Attribute-level constraints and defaults

  2. Key constraints

  3. Referential integrity constraints

  4. Tuple-level (row-level) constraints using CHECK

  5. Naming constraints for better management

Specifying Attribute Constraints and Attribute Defaults

1. NOT NULL Constraint

By default, SQL allows attributes to take the value NULL, which represents missing or unknown information. However, in many cases, NULL values are not acceptable.

  • The NOT NULL constraint prevents an attribute from having NULL values.

  • All attributes that are part of a PRIMARY KEY are automatically NOT NULL.

  • NOT NULL can also be applied to non-key attributes when values are mandatory.

Example:

Salary DECIMAL(10,2) NOT NULL

This ensures that every employee must have a salary value.

2. DEFAULT Clause

The DEFAULT clause assigns a value automatically when a tuple is inserted without specifying that attribute.

  • Default values reduce data-entry errors.

  • If no DEFAULT is specified:

    • The default value is NULL (unless NOT NULL is specified).

Example:

Dno INT NOT NULL DEFAULT 1

If a new employee is inserted without specifying a department number, the department is automatically set to 1.

3. CHECK Constraint on Attributes or Domains

The CHECK clause restricts attribute values to satisfy a logical condition.

  • Applied at:

    • Attribute level

    • Domain level

  • Enforced whenever data is inserted or updated.

Example (attribute-level CHECK):

Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21)

This ensures department numbers are between 1 and 20.

4. CHECK with CREATE DOMAIN

Domains allow reusable constraints across multiple tables.

Example:

CREATE DOMAIN D_NUM AS INTEGER
CHECK (D_NUM > 0 AND D_NUM < 21);

This domain can then be used wherever a department number is required, improving:

  • Consistency

  • Maintainability

  • Readability

 Specifying Key and Referential Integrity Constraints

1. Primary Key Constraint

A PRIMARY KEY uniquely identifies each tuple in a relation.

Characteristics:

  • Must be unique

  • Cannot be NULL

  • Can be:

    • Single attribute

    • Composite (multiple attributes)

Example (single attribute):

Dnumber INT PRIMARY KEY

Example (composite key):

PRIMARY KEY (Essn, Pno)

2. UNIQUE Constraint (Candidate Keys)

The UNIQUE constraint ensures that attribute values are unique but allows NULLs (unless NOT NULL is specified).

  • Used to represent candidate keys

  • A table can have multiple UNIQUE constraints

Example:

Dname VARCHAR(15) UNIQUE

3. Referential Integrity and FOREIGN KEY

A FOREIGN KEY establishes a relationship between two tables.

  • Enforces that a value in the foreign key must:

    • Match a primary key in the referenced table, or

    • Be NULL (if allowed)

Example:

FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)

Referential Triggered Actions

When a referenced tuple is deleted or updated, SQL allows the designer to specify what action should be taken.

a. RESTRICT (Default)

  • Rejects the operation if it causes a violation.

b. SET NULL

  • Sets the foreign key value to NULL.

ON DELETE SET NULL

c. SET DEFAULT

  • Sets the foreign key to its default value.

ON DELETE SET DEFAULT

d. CASCADE

  • Propagates changes automatically.

ON DELETE CASCADE
ON UPDATE CASCADE

Example Interpretation:

  • ON DELETE CASCADE: deleting a department deletes all related projects.

  • ON UPDATE CASCADE: updating a primary key updates all referencing foreign keys.

Design Guideline :

  • CASCADE is suitable for:

    • Relationship tables (WORKS_ON)

    • Weak entity tables (DEPENDENT)

    • Multivalued attributes (DEPT_LOCATIONS)

6.2.3 Giving Names to Constraints

SQL allows constraints to be explicitly named using the CONSTRAINT keyword.

Advantages:

  • Easier to drop or modify constraints

  • Better error messages

  • Improved schema readability

Example:

CONSTRAINT EMPPK PRIMARY KEY (Ssn)

Rules:

  • Constraint names must be unique within a schema

  • Naming is optional but strongly recommended

Specifying Constraints on Tuples Using CHECK

Row-Level (Tuple-Level) CHECK Constraints

CHECK constraints can also be applied at the table level, where they enforce conditions involving multiple attributes in the same row.

  • Evaluated whenever a row is inserted or updated

  • Cannot reference other tables (unless using assertions)

Example:

CHECK (Dept_create_date <= Mgr_start_date)

This ensures that:

  • A manager cannot start managing a department before the department exists.

CHECK vs Assertions

  • CHECK (table-level):

    • Applies to individual tuples

    • Limited in expressive power

  • ASSERTION:

    • Can specify constraints involving multiple tables

    • Uses full SQL queries

    • Discussed later 

Summary 

Constraint TypePurpose
NOT NULL    Disallows NULL values
DEFAULT    Assigns automatic values
CHECK    Restricts allowed values
PRIMARY KEY    Uniquely identifies tuples
UNIQUE    Enforces candidate keys
FOREIGN KEY    Maintains referential integrity
CASCADE / SET NULL / SET DEFAULT    Defines actions on updates/deletes
CONSTRAINT naming    Improves maintainability

Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Advantages of using DBMS Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relationship Types Weak Entity Types Summary of the Notations Used ...
Read more

Data Models, Schemas and Instances

  Data Models, Schemas and Instances  1. Data Abstraction In databases, data abstraction means hiding the complex details of how data is stored and focusing only on what is important. Different users can view data at different levels of detail depending on their needs. 2. Data Models A data model is a collection of concepts used to describe the structure of a database. It defines: Data types Relationships among data Constraints on data Operations for accessing and updating data Data models help us understand and design databases without worrying about low-level storage details. Categories of Data Models a) High-level / Conceptual Data Models Close to how users think about data. Use concepts like entities , attributes , and relationships . Example: ER (Entity–Relationship) Model . b) Representational (Implementation) Data Models In between conceptual and physical models. Easy for users to understand but still close to how data is st...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more