Enforcing Integrity Constraints in DBMS

Integrity Constraints are specified when a relation is created and enforced when a relation is modified. The impact of domain, PRIMARY KEY, and UNIQUE constraints is straightforward: If an insert, delete, or update command causes a violation, it is rejected. Every potential Integrity violation is generally checked at the end of each SQL statement execution, although it can be deferred until the end of the transaction executing the statement.

On the other hand, insertions of Students tuples do not violate referential integrity, and deletions of Students tuples could cause violations.

SQL provides several alternative ways to handle foreign key violations. We must consider three basic questions:

1. What should we do if an Enrolled row is inserted, with a student_id column value that does not appear in any row of the Students table?

In this case, the INSERT command is simply rejected.

2. What should we do if a Students row is deleted?

The options are:

☑      Delete all Enrolled rows that refer to the deleted Students row.

CREATE TABLE table_name (
	column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   FOREIGN KEY (column) REFERENCES table_name1(column) ON DELETE CASCADE
);

☑      Disallow the deletion of the Students row if an Enrolled row refers to it.

CREATE TABLE table_name (
	column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   FOREIGN KEY (column) REFERENCES table_name1(column) ON DELETE NO ACTION
);

☑      Set the studid column to the sid of some (existing) 'default' student, for every Enrolled row that refers to the deleted Students row.

CREATE TABLE table_name (
	column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   FOREIGN KEY (column) REFERENCES table_name1(column) ON DELETE SET DEFAULT
);

3. What should we do if the primary key value of a Students row is updated?

The options here are similar to the previous case. Replace ON DELETE with ON UPDATE

CREATE TABLE table_name (
	column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   FOREIGN KEY (column) REFERENCES table_name1(column) ON UPDATE NO ACTION
);

CREATE TABLE Enrolled (
    Sid int,
    Cid varchar(30),
    joining_date DEFAULT GETDATE()
    FOREIGN KEY (Sid) REFERENCES Students
            ON DELETE CASCADE
            ON UPDATE NO ACTION
);