Insert, Update, and Delete Under Constraints

When using INSERT, UPDATE and DELETE statements on information from more than one table, we're again going dealing with JOIN clauses.

Info

We have a brief overview of SQL JOINs here.

We have both of the tables new_roots and users.

users

id	email	first	last	username
1	colrc@gmail.com	Lawrence	Nicodemus	original
2	jdoe@gmail.com	John	Doe	jdoe
3	jadoe@gmail.com	Jane	Doe	jadoe
null	rdoe@gmail.com	Robert	Doe	rdoe

new_roots

id	userId	english	nicodemus	root
1	1	hello.	a	a
2	2	hey.	aye	a
3	2	boots.	buts	bc
4	3	boots (to be wearing...). (lit. He is wearing boots)	etsbutsbutsshn	bc
5	3	boot. (lit. a borrowed root)	sbutsbutsshn	bc
6	1	rubber boots (putting on...).	sbutsbutsshnmsh	bc
7	1	bull.	buuli	bl
...	...	...	...	...

At this point, all of the new_roots correctly reference a user in the user table.

Now we'll see how to update those connections between rows tables.

Insert a Row With With Constraints

Trying to do this:

INSERT INTO new_roots VALUES(2, null, 'taught (I...h/h).','tsunme''n', 'cnmʔ');

will result in this error:

-- At line 1:
INSERT INTO new_roots VALUES(2, null, 'taught (I...h/h).','tsunme''n', 'cnmʔ');
-- Result: NOT NULL constraint failed: new_roots.userid

We have to supply a reference into the users table, and that reference can't be NULL. Let's pick number 7 (which doesn't exist).

Trying to do this:

INSERT INTO new_roots VALUES(2, 7, 'taught (I...h/h).','tsunme''n', 'cnmʔ');

will result in this error:

-- At line 1:
INSERT INTO new_roots VALUES(2, 7, 'taught (I...h/h).','tsunme''n', 'cnmʔ');
-- Result: UNIQUE constraint failed: new_roots.id

because an id of 2 already exists in the table. PRIMARY KEY constraints imply a UNIQUE constraint.

But wait ... I thought you said that the key could auto-increment the id and add it in for me. What is wrong?

The problem is that we also need to specify that the id fields in both table are AUTOINCREMENT fields.

We can do that by:

• clicking the Database Structure button
• clicking on the new_roots table
• clicking the Modify Structure button
• clicking the AI (auto-increment) column in the id row
• clicking the OK button to change the table

We also want to do this for the users table:

• clicking the Database Structure button
• clicking on the users table
• clicking the Modify Structure button
• clicking the AI (auto-increment) column in the id row
• clicking the OK button to change the table

As an example, here is what that looks like for the new_roots table in DB Browser:

Alright, so let's try to specify a value of NULL for the id field, and get it to be auto-assigned now..

INSERT INTO new_roots VALUES(null, 7, 'taught (I...h/h).','tsunme''n', 'cnmʔ');

will result in this error:

-- At line 1:
INSERT INTO new_roots VALUES(null, 7, 'taught (I...h/h).','tsunme''n', 'cnmʔ');
-- Result: FOREIGN KEY constraint failed

We have an invalid userId column specified. Let's pick one that actually exists. Let's pick a userId of 4:

INSERT INTO new_roots VALUES(null, 4, 'taught (I...h/h).','tsunme''n', 'cnmʔ');

We get this:

-- At line 1:
INSERT INTO new_roots VALUES(null, 4, 'taught (I...h/h).','tsunme''n', 'cnmʔ');
-- Result: query executed successfully. Took 0ms, 1 rows affected

SUCCESS!!

It works! 🎉

Here is what that looks like for the new_roots table in DB Browser:

Delete a Row With With Constraints

How about deleting data? 💥

Okay, let's remove the last entry we just created:

DELETE FROM new_roots WHERE id=31;

This removed the row as desired. No problem.

The current state of the new_roots table:

What happens if we try to remove a user from the users table that is referenced by other roots/words in the new_roots table?

Let's find out. Let's delete the user with an id of 4:

DELETE FROM users WHERE id=4;

The current state of the users table:

We can see that the user with id of 4 has been successfully deleted. But what about new_roots?

The current state of the new_roots table:

Wait.. WHAT HAPPENED? If you look carefully, all of the words in new_roots with userId=4 have ALSO been deleted? Wny?

This is behavior that you sometimes, but RARELY, want. The issue is the specification ON DELETE CASCADE in the FOREIGN KEY specification. SQLite has faithfully done what you told it to. It CASCADED the DELETE, meaning that when you deleted the users entry with an id of 4, it faithfully tracked down all references to any rows in any other tables that referenced it and removed ALL of those rows in other tables as well.

This can be dangerous when unintended. The question is, can we remove this from the constraint so that we don't get this behavior?

Let's remove this behavior.

We can do that by:

• clicking the Database Structure button
• clicking on the new_roots table
• clicking the Modify Structure button
• expand the structure screen so that we can see the FOREIGN KEY constraint and click on it
• remove the action part of the FOREIGN KEY constraint
• click on another column so that the FOREIGN KEY get modified.. weird
• clicking the OK button to change the table

Here are screenshots of the various steps removing the FOREIGN KEY action clause:

Step 1:

Step 2:

Step 3:

Step 4:

Okay, after removing this action from the constraint let's see what happens when we try to delete a user with the id of 3:

DELETE FROM users WHERE id=3;

We get this error:

-- At line 1:
DELETE FROM users WHERE id=3;
-- Result: FOREIGN KEY constraint failed

The users and new_roots tables remain unaffected. The user with id of 3 IS NOT deleted. Because that user is referenced in other tables, and removing them would cause constraint violations in other tables.

The user could still be removed. But only after changing the userId references in the new_roots table to reference other users. When all references have been removed, you can then successfully delete the user.

Recap

This is the behavior you usually want. It provides REFERENTIAL INTEGRITY to the database. None of your references can be lost, and none of your constraints can be violated in this type of database. And if several actions are inside of a transaction, TRANSACTIONAL INTEGRITY makes sure that all of your actions fail or all succeed. And those actions can only succeed if they obey all of your constraints.

Yeah! We have covered a lot. Now you can go forth and design some tables in your own database! 🎉