How to insert new row to database with AUTO_INCREMENT column without specifying column names?

Question

I have a table with the following columns:

• id - INT UNSIGNED AUTO_INCREMENT
• name - VARCHAR(20)
• group - VARCHAR(20)

I know that I can add a row like this:

INSERT INTO table_name (name, group) VALUES ('my name', 'my group')

I wonder if there is a way to add a row without specifying the column names, like when there is no AUTO_INCREMENT column ?

Solution 1

For some databases, you can just explicitly insert a NULL into the auto_increment column:

INSERT INTO table_name VALUES (NULL, 'my name', 'my group')

Solution 2

Even better, use DEFAULT instead of NULL. You want to store the default value, not a NULL that might trigger a default value.

But you'd better name all columns, with a piece of SQL you can create all the INSERT, UPDATE and DELETE's you need. Just check the information_schema and construct the queries you need. There is no need to do it all by hand, SQL can help you out.

Solution 3

Just add the column names, yes you can use Null instead but is is a very bad idea to not use column names in any insert, ever.

Comments

[dburges]

However, this is an extremly bad practice and should not be done. You should always do insert statements with the column names other wise if the columns get rearranged somehow, you will put the data in the wrong column. If another column gets added the insert will fail. I can't enmphasize enough how poor a practice this is. You can destroy your data integrity due to a little laziness. And honestly since you can drag and drog the column names from the object browser, we are talking about risking your data because you were too lazy to spend an extra minute at a task.

[Meredith]

This was helpful for Postgres. Using a NULL results in ERROR: null value in column "id" violates not-null constraint

[techkuz]

if you use python, pass None

[Daniel]

Downvoting because this doesn't answer the question at all, and because for single-use queries in non-production/non-critical environments, it's just fine to not use column names.