Database Constraints Overview

What happens to the 'Dept' column in the Employee table if a referenced department in the Department table is deleted?

Which of the following SQL statements correctly alters the Department table to add a new column?

What does 'on update cascade' do in the context of foreign key constraints?

Which SQL command is used to remove a table along with any dependent structures?

In the given Employee table definition, which columns are part of a unique constraint?

What is the purpose of intra-relational constraints?

Which of the following is NOT an example of intra-relational constraint?

What does the 'unique' constraint do in a database table?

What do foreign keys facilitate in relational databases?

What would happen if a foreign key is violated by deleting a referenced row?

Which reaction policy would you use to prevent updates to a foreign key table when referenced attributes change?

How do you define an inter-relational constraint in SQL?

Which of the following correctly describes the primary key in a database table?

Constraints

Constraints are conditions that must be verified by every database instance.
There are two constraint types: Intra-relational and Inter-relational constraints.

Intra-relational Constraints

These constraints involve a single relation.
Not Null: Ensures an attribute cannot be null.
Unique: Defines keys, can be applied to single or multiple attributes.
- Single Attribute: unique is used after the domain definition.
- Multiple Attributes: unique(Attribute {, Attribute})
Primary Key: Defines the primary key which is unique and cannot be null.
- The syntax is similar to the unique constraint.
Check: Described later.

Inter-relational Constraints

These constraints involve multiple relations.
Check: Described later.
References and Foreign Key: Define Referential integrity constraints.
- Single Attribute: references is applied after the domain definition.
- Multiple Attributes: foreign key (Attribute {, Attribute }) references ...
Reaction policies can be associated with violations of Referential integrity.

Reaction Policies for Referential Integrity Constraints

Reactions apply to the table containing the foreign key after changes to the referenced table.
Violations can be introduced by:
- Updating referenced attributes.
- Deleting rows in the referenced table.
Reactions:
- Cascade: Propagates the change to the related tables.
- Set Null: Nullifies the referring attributes.
- Set Default: Assigns the default value to the referring attributes.
- No Action (Restrict): Forbids changes to the external table.
Reactions depend on the event, syntax: on < delete | update > < cascade | set null | set default | no action >

Schema Updates

Two SQL statements are used for schema updates:
- Alter: Used to modify domains, tables, views, and assertions.
- Drop: Used to remove schemas, domains, tables, views, assertions.
Syntax:
- drop < schema | domain | table | view | assertion > ComponentName [ restrict | cascade ]
Example:
- Alter table Department add column NoOfOffices integer
- Drop table TempTable cascade

Explore the fundamental types of constraints in databases, focusing on intra-relational and inter-relational constraints. Learn about Not Null, Unique, Primary Key, and Foreign Key constraints that ensure data integrity. This quiz will test your understanding of these essential database concepts.