> if I do:
> INSERT INTO TABLE atable VALUES('SQL1','Jason')
> the 'id' gets updated with a new number automatically.
Correct, the default value is the next value in the sequence.
> I then later
> added a new column called 'date'. Now if I do an insert with:
> INSERT INTO TABLE atable VALUES('SQL2','Toy','',date('now'))
> the id will update the first time to '0'
That is because you are specifying a value for the id field, which means
that it *doesn't* use the default value (which is the next number in the
sequence). I'm surprised that this will work... I suppose that it is casting
the empty string to zero, which is the value it is inserting.
> , but using this command again:
> INSERT INTO TABLE atable VALUES('SQL3','Toy','',date('now'))
> it won't let me update because there are duplicate 'id's.
That is because it thinks you are explicitly using a zero value again... and
when you use the explicit value, it ignores the default.
> I also tried
> INSERT INTO TABLE a(title, name, date) VALUES('SQL3','Toy',date('now'))
> but it won't automatically update 'id' also.
I'm surprised that works at all... the name of the table is "atable", isn't
it? Try this:
INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
Greg