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
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.
NULL and 0 as values for an auto_increment column, and interprets them as meaning "use the next ID".
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)
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.
INSERT INTO `table` () VALUES ();
is working too.
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));
