MacLochlainns Weblog

I found myself explaining the nuances of INSERT statements and whether you should use named or positional notation. While the class was on Zoom, I could imagine the blank stares in the silence of my headphones. Then, I had to remind them about mandatory (NOT NULL constrained) and optional (nullable) columns in tables and how an INSERT statement requires an explicit NULL value for optional columns when the INSERT statement isn’t inserting a value into that column.

Then, I asked if somebody could qualify the different types of INSERT statements; and what would happen if a table with a first_name and last_name column order evolves when a new DBA decides to restructure the table and uses a last_name and first_name column order in the new table structure. Only a couple of the students recalled using a column-list between the table name and VALUES clause but none could remember how to write an INSERT-SET statement.

Below is a quick example of inserting data with column-lists and the SET clause. It builds an actor table with an actor_id column as a surrogate key and primary key column and a unique natural key composed of the first and last name columns (not a real world solution for uniqueness).

CREATE TABLE actor
( actor_id    int unsigned primary key auto_increment
, first_name  varchar(30) not null
, last_name   varchar(30) not null
, CONSTRAINT  actor_uq UNIQUE (first_name, last_name));

Next, let’s insert a couple rows with a column-list approach. The column-list is a comma-delimited list of column values that must contain all mandatory columns and may contain optional columns.

INSERT INTO actor
( first_name
, last_name )
VALUES
 ('Harrison','Ford')
,('Carrie','Fisher')
,('Mark','Hamill')
,('Alec','Guinness');

Now, let’s validate the unique constraint on the composite value of first and last name by trying to insert a second Harrison Ford into the actor table.

INSERT INTO actor
(first_name, last_name)
VALUES
('Harrison','Ford');

it fails and raises the following error:

ERROR 1062 (23000): Duplicate entry 'Harrison-Ford' for key 'actor.actor_uq'

The following uses the INSERT-SET statement to add Tom Hanks to the actor table:

INSERT INTO actor
SET first_name = 'Tom'
,   last_name  = 'Hanks';

I believe the INSERT-SET is the best approach to a name-value model for INSERT statements. It’s a shame that only MySQL supports it. Query the actor table with the following:

SELECT   *
FROM     actor
ORDER BY actor_id;

it returns:

+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Harrison   | Ford      |
|        2 | Carrie     | Fisher    |
|        3 | Mark       | Hamill    |
|        4 | Alec       | Guinness  |
|        6 | Tom        | Hanks     |
+----------+------------+-----------+
5 rows in set (0.01 sec)

There’s now a gap when you query the data because the second attempt at adding Harrison Ford consumed a sequence value from the internally managed list. That list is a property of the table when you create or alter a table to include an autoincrementing column, like actor_id. Anytime you attempt to insert a row that violates the table constraints, you consume a value from the internally managed sequence. While you can restore it and eliminate the gaps, I strongly recommend against it.

As always, I hope this helps those trying to learn and experiment with syntax.