Have you run into the 'default fault'?
mysql> create table foo (t1 int not null, note char(10) default 'none');
Query OK, 0 rows affected (0.01 sec)
mysql> insert into foo values (1,'some');
Query OK, 1 row affected (0.01 sec)
mysql> insert into foo values (2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql>
So why isn't the default value of 'none' automatically entered into the table? If you are an old hand at MySQL, you probably are smiling right now and remembering the first time you whacked you metaphorical toe on this situation. There is a question similar to this in the MySQL exam bank that separates the experienced pros from the newbies.
Okay, so how do you get the default value into the note field?
Hint:
mysql> insert into foo (t1) values (2);
Query OK, 1 row affected (0.00 sec)
mysql>
The table foo has two columns. Now 'insert into foo values (...' statement tells the server that you are going to have one column of data for each column in the table, even the ones with a default value.
The 'insert into foo (t1) values ...' statement tells the server you have one column of data and the server will graciously fill other columns with any default values you have specified.
2 comments:
The reason behind this is shown when multiple columns have default values. If you don't tell MySQL which columns are being updated it doesn't know which you meant.
Best practices are that you should always explicitly define which columns you are setting because a table can always be changed in the future to have more columns and would cause this sort of error.
mad props to john j for throwing in that absolutely correct best practice.
Post a Comment