CODEWITHSUNDEEP
mysql
developarvin
developarvin

Reputation: 5069

Mysql -- inserting in table with only an auto incrementing column

Lets say we have table A with just one column, id(which is the primary key)

How do we insert a new row into the table without specifying an id?

I tried this

INSERT INTO A (`id`) VALUES (NULL)

and it doesn't work

Edit: I forgot to mention that id, the primary key has the auto_increment and NOT NULL attribute.

Edit 2: The exact error when running the query above is 

Column 'id' cannot be null

Upvotes: 12

Views: 14230

Answers (5)

Lloyd
Lloyd

Reputation: 76

INSERT INTO `table` () VALUES ();

is working too.

Upvotes: 0

francisco
francisco

Reputation: 1

I had a similar issue, then I noticed that I didn't apply the changes when I changed id to primary key + not null + auto incremental.

Upvotes: 0

Sebastien
Sebastien

Reputation: 264

As soon as 'id' as the auto-increment enable (assuming ID is an integer), you can just do:

INSERT INTO A (id) values (null)

and 'id' will keep incrementing each time.

Upvotes: 19

Jon Black
Jon Black

Reputation: 16569

only works if you're using an auto_increment primary key (PK) as every PK must have a unique, non null value.

drop table if exists A;
create table A
(
id int unsigned not null auto_increment primary key
)
engine=innodb;

insert into A (id) values (null),(null);

mysql> select * from A order by id;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

Upvotes: 6

lightsong
lightsong

Reputation: 391

Try it without the ``.. As in:

INSERT INTO A(sid) VALUES(NULL); //i used sid instead of id...

worked fine for me..

Also wwhile creating the table A, specify unique(sid)... i.e

create table A(sid int(3) not null auto_increment unique(sid));

Upvotes: -1

Related Questions