SQL Issue: INSERT without column_list

in sql •  7 years ago 

When reviewing application code, I often see INSERT queries that don't specify a column list.

INSERT INTO Table1
VALUES ('Val1', 'Val2', 'Val3'); 

This syntax works great. However, it has an implied dependency on the column definitions of the table. If the table's schema changes, the INSERT would fail... or worse, it would succeed and put the values into the wrong columns.

I normally see this error in application code. In applications, the developer has control over their environment, so assumptions like this are relatively safe. However, in a world where application stitch together data from different sources, assumptions like this can be dangerous. When interacting with any service, it's best to be defensive.

So even though it's a bit more work, specify the column list when INSERTing.

INSERT INTO Table1 (Col1, Col2, Col3)
VALUES ('Val1', 'Val2', 'Val3'); 
Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

f7a50c768a56484a0d43ecd82464feb1.jpg

Congratulations @pretentious! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 1 year!

Click here to view your Board

Support SteemitBoard's project! Vote for its witness and get one more award!

Congratulations @pretentious! You received a personal award!

Happy Birthday! - You are on the Steem blockchain for 2 years!

You can view your badges on your Steem Board and compare to others on the Steem Ranking

Vote for @Steemitboard as a witness to get one more award and increased upvotes!