In SQL, referential constraints are used to ensure the integrity of data between tables. This is most commonly achieved using Foreign Key constraints, which establish a relationship between two tables. The Foreign Key in one table refers to the Primary Key in another table. This ensures that records cannot be added to the table with the Foreign Key unless there is a corresponding record in the table it refers to. Similarly, records in the table that is being referred to (i.e., the table with the Primary Key) cannot be deleted or modified in a way that would break the relationship.

Types of Referential Constraints

1. CASCADE: Deleting or updating a record in the primary table will automatically delete or update the matching records in the foreign key table.

2. SET NULL: Deleting or updating a record in the primary table will set the foreign key field(s) in the foreign table to NULL.

3. NO ACTION: Do nothing if the related primary key is modified or deleted. This will usually result in an error if there are dependent records.

4. SET DEFAULT: Delete or update the row from the parent table and set the foreign key column or columns in the child table to their default values.

5. RESTRICT: Rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table.

Workshop Exercise: MySQL

To help understand the concept better, let's walk through a simple workshop exercise using MySQL.

Setup

1. Open your MySQL terminal or GUI-based client.

2. Create a database and use it.

    CREATE DATABASE referentialDemo;
    USE referentialDemo;
   

   To use in the DBeaver GUI client, don't forget to grant permissions to the database:

    GRANT ALL PRIVILEGES ON referentialDemo.* TO 'user'@'localhost';
    FLUSH PRIVILEGES;
   

Step 1: Create Tables

Create two tables authors and books. The authors table will contain a primary key id, and the books table will contain a foreign key author_id that references authors(id).

CREATE TABLE authors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(50),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

Step 2: Insert Data into authors

INSERT INTO authors (name) VALUES ('J.K. Rowling'), ('George Orwell'), ('J.R.R. Tolkien');

Step 3: Insert Data into books with Valid Foreign Key

This should succeed because the author_id exists in the authors table.

INSERT INTO books (title, author_id) VALUES ('1984', 2);

Step 4: Try to Insert Data into books with Invalid Foreign Key

This should fail because there's no author_id 5 in the authors table.

INSERT INTO books (title, author_id) VALUES ('Invalid Book', 5);

Step 5: Delete a Record from authors

Try deleting an author and see what happens.

DELETE FROM authors WHERE id = 2;

This will fail because a book (1984) is referring to this author.

Step 6: Update Foreign Key Constraint to Cascade

Let's update the foreign key to cascade deletes.

ALTER TABLE books DROP FOREIGN KEY books_ibfk_1;

ALTER TABLE books ADD CONSTRAINT books_ibfk_1 FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE;

The SQL statement ALTER TABLE books DROP FOREIGN KEY books_ibfk_1; is used to remove a foreign key constraint from an existing table. Let's break down the syntax to understand each part:

• ALTER TABLE books: This part tells MySQL that you want to make a change to the structure of the table named books.

• DROP FOREIGN KEY: This is the action you want to perform. In this case, you want to remove a foreign key constraint from the table.

• books_ibfk_1: This is the name of the foreign key constraint you want to remove. This is a system-generated name if you didn't specify one when creating the foreign key.

When you execute this command, MySQL will remove the foreign key constraint named books_ibfk_1 from the books table. This will uncouple the table from any other table it is related to via this specific foreign key constraint, and you'll be able to delete or modify data in the books and authors tables independently of each other, without MySQL enforcing this specific referential integrity constraint.

Step 7: Delete a Record from authors Again

Now try deleting an author again.

DELETE FROM authors WHERE id = 2;

This time the delete will succeed, and the corresponding record in books will be deleted as well.

Step 8: Confirm Deletion

Confirm that the book with author_id = 2 is deleted.

SELECT * FROM books WHERE author_id = 2;

The query should return an empty set.

And there you go! You've tested some of the basic concepts of referential constraints in MySQL.

References

1. Referential constraints

2. What does 'ibfk' stand for in MySQL?