Jeff Smelser wrote:
> On Tuesday 08 February 2005 03:32 pm, Emmett Bishop wrote:
>
>
>>is it possible to force a field to be NOT NULL but not
>>have any default value (I.E the insert statement must
>>explicitly provide data for the field in question)?
>
>
> of course: col1 int not null
>
> Jeff
Except that ALL columns in mysql (prior to 5.0.2) have defaults. See the
manual <http://dev.mysql.com/doc/mysql/en/constraint-invalid-data.html>.
For example:
mysql> CREATE TABLE deftest (col1 INT NOT NULL, col2 CHAR(1));
Query OK, 0 rows affected (0.00 sec)
This works as expected
mysql> INSERT INTO deftest VALUES (NULL, 'a');
ERROR 1048: Column 'col1' cannot be null
but this
mysql> INSERT INTO deftest (col2) VALUES ('b');
Query OK, 1 row affected (0.01 sec)
and this
mysql> INSERT INTO deftest VALUES (3, 'c'), (NULL, 'd');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 1
may surprise you.
mysql> SELECT * FROM deftest;
+------+------+
| col1 | col2 |
+------+------+
| 0 | b |
| 3 | c |
| 0 | d |
+------+------+
3 rows in set (0.00 sec)
Michael
