Re: How to define a required field in a table?

[Michael Stassen]

bluejack wrote:

On Tue, 28 Oct 2003 14:00:02 -0800, Kevin Fries <[EMAIL PROTECTED]> wrote:

That's not how it works.  The following demonstrates the feature I think
you want.
mysql test
test> create table testnull ( X varchar(10) not null );
Query OK, 0 rows affected (0.11 sec)
test> insert into testnull values ( null );
ERROR 1048: Column 'X' cannot be null
test>


One other case: If there is a default value on the column,
the default will be inserted instead of NULL.
-bluejack

There is always a default in mysql, but I imagine you mean the case 
where you have explicitly set a default value in the table definition. 
If so, what you've said isn't quite right.  The default you set will be 
used if you leave out the column, but 0 or '' will be used if you try to 
set an illegal value such as NULL.  Consider (using mysql 4.0.15):

mysql> create table testnull (X int NOT NULL default 5,
    ->                        Y varchar(10) default 'empty' not null);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into testnull values ();
Query OK, 1 row affected (0.00 sec)
mysql> insert into testnull values
    ->       (1, NULL),
    ->       (NULL, 'two'),
    ->       (NULL, NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 4
mysql> select * from testnull;
+---+-------+
| X | Y     |
+---+-------+
| 5 | empty |
| 1 |       |
| 0 | two   |
| 0 |       |
+---+-------+
4 rows in set (0.01 sec)
Michael



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]