SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Antipattern: A Shortcut That Gets You Lost

Antipattern: A Shortcut That Gets You Lost

Let’s understand how using implicit columns creates issues in a database.

We'll cover the following...

Although using wildcards and unnamed columns satisfies the goal of less typing, this habit creates several hazards.

Breaking refactoring

Let’s suppose we need to add a column to the Bugs table, such as date_due, for scheduling purposes.

ALTER TABLE Bugs ADD COLUMN date_due DATE;

But our INSERT statement results in an error because we listed eleven values instead of the twelve the table now expects.

MySQL
INSERT INTO Bugs 
     VALUES (DEFAULT, CURDATE(), 'New bug', 'Test T987 fails...', 
            NULL, 123, NULL, NULL, DEFAULT, 'Medium', NULL);

In an INSERT statement that uses implicit columns, we must give values for all the columns in the same order that the columns are defined in the table. If the ...