Portable SQL/Common/DBMS Differences

Differences between implementations

NULL in column definition

This table is originally from "Building Truly Portable Database Applications in PHP": NULL

Database	Default value	Definition allowed	Notes
Firebird	NULL	no
MySQL	NULL	yes
Oracle	NULL	yes	Empty strings considered NULL
PostgreSQL	NULL	yes
SQL Server	NOT NULL	yes
SQLite	NULL	yes
Sybase ASE	NOT NULL	yes

The "Definition allowed" column indicates if specifying NULL in table column definitions is allowed for CREATE TABLE statements - NOT NULL is always allowed. If "yes", the following statement doesn't generate an error:

CREATE TABLE mytable ( id INTEGER NULL )

Identifiers

Parts of this table are originally from "Building Truly Portable Database Applications in PHP": Column Name Case

Database	Length	Allowed	Case	Notes
Firebird	31	L,D	upper	case preserved with double quotes (")
MySQL	63	L,NL,D,_,$	preserved
Oracle	30	L,NL,D,_,$,#	upper	Info, max. DB name length is 8
PostgreSQL	63	L,NL,D,_,$	lower	case preserved with double quotes (")
SQL Server	127	L,NL,D,_,$,@,#	preserved	Info
SQLite	255	L,NL,D,_	preserved
Sybase ASE	131	L,NL,D,_,$,@,#	preserved	max. DB and Cursor name length is 28

In general, all identifiers must start with a letter or non-latin letter; digits and other symbols as first characters are often not allowed or change their meaning.

Since OpenDBX 1.1.4 the use of double quotes (") for identifiers (table and column names) is suggested. This complies to ANSI standards and is the only way to use reserved words and prevent clashes in the future if language constructs are extended by the database vendors.