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.

Press + to interact
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.

Press + to interact
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 ...