Title: Use the CASCADE statement to update foreign key data in MariaDB in Python

[The SQL CASCADE clause lets changes to one table affect constrained tables in Python]

A few times in other posts, I've used foreign key constraints. For example, the following code creates two tables: Authors and Books. The Books table has a foreign key constraint that requires its AuthorId values to be present in the Authors table's AuthorId values. That prevents you from creating a book that doesn't have a valid author.

# Create the Authors table. command = ''' CREATE OR REPLACE TABLE Authors ( AuthorId INT NOT NULL AUTO_INCREMENT, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, PRIMARY KEY (AuthorId) )''' cur.execute(command) # Create the Books table. command = ''' CREATE OR REPLACE TABLE Books ( AuthorId INT NOT NULL, Title VARCHAR(100) NOT NULL, INDEX AuthorId (AuthorId), FOREIGN KEY author_fk (AuthorId) REFERENCES Authors(AuthorId) )''' cur.execute(command)

This has several consequences.

You cannot create a Books record unless the necessary AuthorId value is already in the Authors table.
You cannot delete an Authors record if its AuthorId value is used in a Books record.
You cannot change an Authors record's AuthorId value if it is used in a Books record.

That last one is particularly troublesome. For example, one way to modify an Authors table's AuthorId would be to:

Create a new Authors record with the same values as the original record but the new AuthorId.
Update the corresponding Books records to use the new AuthorId.
Delete the original Authors record.

The SQL CASCADE clause gives you better options.

ON DELETE CASCADE

If you create the constrained table with the clause ON DELETE CASCADE, then deletions in the foreign table are carried into the constrained table. Here's SQL code that creates the Books table with that clause.

CREATE OR REPLACE TABLE Books ( AuthorId INT NOT NULL, Title VARCHAR(100) NOT NULL, INDEX AuthorId (AuthorId), FOREIGN KEY author_fk (AuthorId) REFERENCES Authors(AuthorId) ON DELETE CASCADE

Now if you delete an Authors record, the database automatically deletes any Books records that uses the same AuthorId.

ON UPDATE CASCADE

If you create the constrained table with the clause ON UPDATE CASCADE, then changes to the constrained field in the foreign table are carried into the constrained table. Here's SQL code that creates the Books table with that clause.

CREATE OR REPLACE TABLE Books ( AuthorId INT NOT NULL, Title VARCHAR(100) NOT NULL, INDEX AuthorId (AuthorId), FOREIGN KEY author_fk (AuthorId) REFERENCES Authors(AuthorId) ON UPDATE CASCADE

Now if you update an Authors record's AuthorId field, then any Books records that have that same AuthorId value are automatically updated to match.

Example Code

To make testing these clauses easier, I wrote the following create_tables method.

def create_tables(cur, on_clause=''): '''Create the tables.''' # Either create the tables in order so the constraints are not violated # or create the tables first and then alter them to add the constraints. # Create the Authors table. command = ''' CREATE OR REPLACE TABLE Authors ( AuthorId INT NOT NULL AUTO_INCREMENT, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, PRIMARY KEY (AuthorId) )''' cur.execute(command) # Create the Books table. command = f''' CREATE OR REPLACE TABLE Books ( AuthorId INT NOT NULL, Title VARCHAR(100) NOT NULL, INDEX AuthorId (AuthorId), FOREIGN KEY author_fk (AuthorId) REFERENCES Authors(AuthorId) {on_clause} )''' cur.execute(command)

This code composes and executes two SQL statements that create the Authors and Books tables. The on_clause parameter should be blank, ON DELETE CASCADE, or ON UPDATE CASCADE. The method adds that parameter to the statement that creates the Books table to demonstrate its effect.

The main program uses that method three times to demonstrate all of the variations. First it executes this code.

# Recreate the tables. drop_tables(cur) create_tables(cur) insert_records(cur) # Try to delete an Authors record. try: cur.execute('DELETE FROM Authors WHERE FirstName="Terry"') except mariadb.Error as e: print(f'MariaDB error: {e}\n') finally: show_records(cur)

This time there is no CASCADE clause. This deletion violates the foreign key constraint, so the program displays the following error message.

MariaDB error: Cannot delete or update a parent row: a foreign key constraint fails (`library_db`.`books`, CONSTRAINT `author_fk` FOREIGN KEY (`AuthorId`) REFERENCES `authors` (`AuthorId`))

The show_records function then displays the unmodified data. It shows the AuthorId, FirstName, and LastName from the Authors table, plus the AuthorId and Title from the Books table.

1 Rod, Stephens, 1, Beginning Database Design Solutions, Second Edition 1 Rod, Stephens, 1, Build Your Own Ray Tracer With Python 1 Rod, Stephens, 1, Beginning Software Engineering, Second Edition 1 Rod, Stephens, 1, Interview Puzzles Dissected 2 Terry, Pratchett, 2, Going Postal 2 Terry, Pratchett, 2, Making Money 3 Christopher, Moore, 3, Lamb: The Gospel According to Biff, Christ's Childhood Pal 3 Christopher, Moore, 3, Fool 3 Christopher, Moore, 3, The Serpent of Venice 3 Christopher, Moore, 3, Shakespeare for Squirrels

Next, the program uses the following code to demonstrate the ON DELETE CASCADE clause.

# Recreate the tables with ON DELETE CASCADE. drop_tables(cur) create_tables(cur, "ON DELETE CASCADE") insert_records(cur) # Delete an Authors record. try: cur.execute('DELETE FROM Authors WHERE FirstName="Terry"') except mariadb.Error as e: print(f'MariaDB error: {e}\n') finally: show_records(cur)

When the code deletes Terry Pratchett's Authors record, the database automatically deletes his Books records, too. Here's what's left of the database after these deletions.

1 Rod, Stephens, 1, Beginning Database Design Solutions, Second Edition 1 Rod, Stephens, 1, Build Your Own Ray Tracer With Python 1 Rod, Stephens, 1, Beginning Software Engineering, Second Edition 1 Rod, Stephens, 1, Interview Puzzles Dissected 3 Christopher, Moore, 3, Lamb: The Gospel According to Biff, Christ's Childhood Pal 3 Christopher, Moore, 3, Fool 3 Christopher, Moore, 3, The Serpent of Venice 3 Christopher, Moore, 3, Shakespeare for Squirrels

Finally, the program uses the following code to demonstrate ON UPDATE CASCADE.

# Recreate the tables with ON UPDATE CASCADE. drop_tables(cur) create_tables(cur, "ON UPDATE CASCADE") insert_records(cur) # Update an Authors.AuthorId value. try: cur.execute('UPDATE Authors SET AuthorId = 100 WHERE AuthorId=1') except mariadb.Error as e: print(f'MariaDB error: {e}\n')

finally: show_records(cur)

This code changes the AuthorId value from 1 to 100 in the Authors table's record. The database automatically updates the corresponding Books records giving the following result.

2 Terry, Pratchett, 2, Going Postal 2 Terry, Pratchett, 2, Making Money 3 Christopher, Moore, 3, Lamb: The Gospel According to Biff, Christ's Childhood Pal 3 Christopher, Moore, 3, Fool 3 Christopher, Moore, 3, The Serpent of Venice 3 Christopher, Moore, 3, Shakespeare for Squirrels 100 Rod, Stephens, 100, Beginning Database Design Solutions, Second Edition 100 Rod, Stephens, 100, Build Your Own Ray Tracer With Python 100 Rod, Stephens, 100, Beginning Software Engineering, Second Edition 100 Rod, Stephens, 100, Interview Puzzles Dissected

Now my books' AuthorId values are 100 in both tables.

If you've been paying attention (and I don't blame you if it's after lunch and you're falling asleep at your desk), you may ask, "What happens if you try to update an Authors record so the AuthorId matches a value used by another record? For example, if we changed the "Rod Stephens" record's AuthorId to 2 so Rod and Sir Terry have the same AuthorId?"

Good question!

The ON UPDATE CASCADE clause won't prevent that, but the Authors table's primary key will! The SQL code that created that table included the clause PRIMARY KEY (AuthorId), which makes the AuthorId field the table's primary key. The values of a primary key must be unique in a table, so the database won't allow us to change Rod's AuthorId value so it's the same as Sir Terry's value and the crisis is averted.

Conclusion

The ON DELETE CASCADE and ON UPDATE CASCADE clauses make it easier to deal with changes to constrained fields. There are a few disadvantages to using these statements. For example, they mean a small change to one record may cascade into changes in many other records, potentially slowing performance and making things like transactions (which I'll talk about in a later post) longer. If you delete a record from the foreign table, you may also delete many other constrained records. If you mess it up, you can do a lot of damage very quickly.

For those reasons, some programmers prefer to update or delete constrained records with separate code so they need to think through the changes more carefully. You can still do a lot of damage, however.

I think in general, these clauses are helpful and can save you some work, but you need to be careful either way.

Download the example to experiment with it and to see additional details.

For a lot more information about database design and implementation, see my book Beginning Database Design Solutions, Second Edition.