User:Jack Phoenix/Porting to alternative DBMSes

Quick and dirty guide on how to convert MySQL-specific SQL files to be compatible with alternative DBMSes. Well, not exactly convert, since you have to create a new .sql file for each alternative DBMS, but you get the idea anyway.

This is not and will never be an exhaustive guide; this is just a glimpse at creating schemas for non-MySQL/MariaDB backends for developers like myself who have absolutely no experience with (nor interest in) PostgreSQL/Oracle/MSSQL.

See also r45764 by Tim Starling, committed on 15 January 2009. Diff on ViewVC

Dealing with automatic ID assignement
For MySQL and SQLite, use  type.

Do not assume that INSERTing a row with omitted primary key autogenerates the value (as it does for MySQL and sometimes for PostgreSQL).

For PostgreSQL and ORACLE you have to create a sequence object. Please observe naming conventions - for PostgreSQL it all can be simplified by using  pseudo-datatype, which automagically creates a sequence and assigns the default value to the column. For Microsoft SQL, those objects are called  and contain   data type column called.

Use this database-agnostic pattern in PHP to deal with primary key values: