gistfile1.txt

mysql> create table foo (id integer primary key auto_increment not null, port integer uniqu
e null);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo (port) values (200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (port) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo (port) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+----+------+
| id | port |
+----+------+
|  1 |  200 | 
|  2 | NULL | 
|  3 | NULL | 
+----+------+
3 rows in set (0.00 sec)

mysql> ^DBye

----------

CREATE TABLE  `ammon`.`nulltest` (
    `oid` INT NOT NULL AUTO_INCREMENT ,
    `host` VARCHAR( 64 ) NOT NULL ,
    `port` INT NULL ,
    PRIMARY KEY ( `host` , `port` , `oid` )
)

mysql> insert into nulltest (host,port) values ('foo',100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into nulltest (host,port) values ('foo',101);                    
Query OK, 1 row affected (0.00 sec)

mysql> insert into nulltest (host,port) values ('foo',null);                   
ERROR 1048 (23000): Column 'port' cannot be null

mysql> describe nulltest;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| oid   | int(11)     | NO   | PRI | NULL    | auto_increment | 
| host  | varchar(64) | NO   | PRI | NULL    |                | 
| port  | int(11)     | NO   | PRI | 0       |                | 
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

----------

CREATE TABLE  `ammon`.`nulltest` (
    `oid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `host` VARCHAR( 64 ) NOT NULL ,
    `port` INT NULL ,
    UNIQUE (
        `host` ,
        `port`
    )
)

mysql> insert into nulltest (host,port) values ('foo',100);                    
Query OK, 1 row affected (0.00 sec)

mysql> insert into nulltest (host,port) values ('foo',101);                    
Query OK, 1 row affected (0.00 sec)

mysql> insert into nulltest (host,port) values ('foo',null);                   
Query OK, 1 row affected (0.00 sec)

mysql> insert into nulltest (host,port) values ('foo',null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from nulltest;
+-----+------+------+
| oid | host | port |
+-----+------+------+
|   1 | foo  |  100 | 
|   2 | foo  |  101 | 
|   3 | foo  | NULL | 
|   4 | foo  | NULL | 
+-----+------+------+
4 rows in set (0.00 sec)

mysql> describe nulltest;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| oid   | int(11)     | NO   | PRI | NULL    | auto_increment | 
| host  | varchar(64) | NO   | MUL | NULL    |                | 
| port  | int(11)     | YES  |     | NULL    |                | 
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)