[TYPO3-dev] Default definitions of database fields

[Jigal van Hemert]

Jochen Rau wrote:
> 3. You should add a NOT NULL statement, if that suits the domain as 
> fields allowing NULL can not be indexed and have some other 
> disadvantages. But you _can_ allow NULL if that makes sense in the 
> domain to be modeled.

Column which allow NULL values can be indexed. Restrictions [1]:

----------------
KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can 
also be specified as just KEY when given in a column definition. This 
was implemented for compatibility with other database systems.

A UNIQUE index creates a constraint such that all values in the index 
must be distinct. An error occurs if you try to add a new row with a key 
value that matches an existing row. For all engines, a UNIQUE index 
allows multiple NULL values for columns that can contain NULL.

A PRIMARY KEY is a unique index where all key columns must be defined as 
NOT NULL. If they are not explicitly declared as NOT NULL, MySQL 
declares them so implicitly (and silently). A table can have only one 
PRIMARY KEY. If you do not have a PRIMARY KEY and an application asks 
for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index 
that has no NULL columns as the PRIMARY KEY.
-----------------

So, only for PRIMARY KEYs all columns must be NOT NULL, both INDEX and 
UNIQUE can have NULL values.

There can be another reason to set a certain length on an integer field. 
  If you want to use FOREIGN KEY CONSTRAINTs with InnoDB tables (InnoDB 
is the only storage engine which currently supports foreign key 
constraints) [2]:
-----------------
Corresponding columns in the foreign key and the referenced key must 
have similar internal data types inside InnoDB so that they can be 
compared without a type conversion. The size and sign of integer types 
must be the same. The length of string types need not be the same. For 
nonbinary (character) string columns, the character set and collation 
must be the same.
-----------------

[1] http://dev.mysql.com/doc/refman/5.1/en/create-table.html
[2] 
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
--
Jigal van Hemert.