Insert values without mentioning the column name

Question

So i have tried inserting the values without mentioning the column name in the code..

After some searching, I have found that it is possible by passing the value as null as mentioned here.But when I did it, it threw some errors.

The code i have used

CREATE TABLE Persons
(
    ID int  NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (ID)
);

insert into Persons values (NULL,1);

So how canI add the values without mentioning the column name?

Thanx in advance

Answer 1

If this is just for experimental purposes yes you can:

INSERT INTO Persons VALUES (NULL, 'Lastname', 'Firstname', 'Address test', 'city of angels');

But I strongly urge you to not make it a habit of doing this. Always add columns, as eloquently stated by David's answer.

INSERT INTO Persons (LastName, FirstName, Address, City) VALUES ('Last', 'First', 'Add', 'City');

Fiddle

Answer 2

If you don't specify which columns are supposed to get those values, how do you expect the database to know? If you at least supply values for all columns then maybe the query engine can infer by the ordering of the columns, depending on the database. (Which, according to this question and its answers, seems to be supported in MySQL.)

But even then, that's not great practice. It creates all sorts of coupling between the schema and the code. By specifying the columns explicitly you can later add/change unrelated columns in the same table without affecting that query. But if you rely on the ordering of columns in the schema then any change to the schema will require changing every query, even ones which are logically unaffected.

It's simpler, more stable, and expresses intent more clearly to specify the columns explicitly.