One of the aims of Flourish is to make a portable PHP library so that applications can be deployed on different servers with little to no changes. With the multitude of database servers available and different platforms, one of the main hurdles for portability is the different dialects of SQL.
Flourish SQL has grown a common subset of the SQL dialects supported by IBM DB2, Microsoft SQL Server, MySQL, PostgreSQL and SQLite, with a few additions for ease-of-use. This document describes what features and syntax are supported across all databases.
In the Flourish SQL column you will find the supported syntax for a function, data type, etc. The other columns contain a ✓
if the syntax is the same, or the database-specific syntax highlighted in red.
The information on this page is targeted at the following database versions, or newer editions:
If targeting MSSQL or DB2, please be aware that UNIQUE
constraints only allow a single NULL
value to be present in all rows. To work around this, split the column in to a separate table and use a foreign key to associate it to the original table.
Oracle does not support the ON UPDATE
clause for foreign keys. MSSQL does not allow ON UPDATE CASCADE
or ON DELETE CASCADE
clauses for foreign keys that could cause cycles during handling. This is often caused by multiple columns in a table referencing the same foreign table.
MySQL, DB2, Oracle and MSSQL all have various constraints on TEXT
and BLOB
data types. To achieve cross-database support, be sure not to do the following with such columns:
DEFAULT
valueGROUP BY
clausePRIMARY KEY
or FOREIGN KEY
UNIQUE
constraint
Since fRecordSet uses GROUP BY
clauses to allow for selection of records by values in related tables, it is highly recommended that TEXT
and BLOB
columns always be split out into a separate table and to use a foreign key to associate it with the original table. The Flourish ORM has strong support to easily retrieving such related records.
The following Flourish SQL is supported when calling the fDatabase::translatedQuery() and fDatabase::unbufferedTranslatedQuery() methods. The Cross-Database SQL section of the fDatabase page has more information.
Most dialects of SQL support a large number of common data types, however a things like booleans, binary data and dates and times are often different. Here is an outline of the data types supported by Flourish SQL:
Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
---|---|---|---|---|---|---|
smallint | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
integer | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
bigint | ✓ | ✓ | ✓ | integer | ✓ | ✓ |
integer autoincrement primary key | integer generated by default as identity primary key | integer identity(1) primary key | integer auto_increment primary key | integer + sequence + trigger | serial primary key | integer primary key autoincrement |
float | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
real | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
decimal | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
char | ✓ | nchar | ✓ | ✓ | ✓ | ✓ |
varchar | ✓ | nvarchar | ✓ | varchar2 | ✓ | ✓ |
text | clob | ntext | mediumtext | clob | ✓ | ✓ |
blob | ✓ | image | longblob | ✓ | bytea | ✓ |
timestamp | ✓ | datetime | datetime | ✓ | ✓ | ✓ |
date | ✓ | date for 2008, datetime for 2005 | ✓ | ✓ | ✓ | ✓ |
time | ✓ | time for 2008, datetime for 2005 | ✓ | timestamp | ✓ | ✓ |
boolean | char(1) | bit | ✓ | number(1) | ✓ | ✓ |
In all cases except for the date
and time
are the data types consistent across the databases. MSSQL does not support date
or time
, so the more precise datetime
is used instead. Oracle does not support time
and uses timestamp
instead. In both cases, the unnecessary portions are removed by the unescape()
method of fDatabase.
One other thing to note is the apparent perfect compatibility for SQLite data types. This is not because the SQLite data types were chosen as the basis for Flourish SQL, but rather that SQLite is loosely-typed and allows anything to be entered for a data type.
Since boolean fields are implemented quite differently across databases, it comes as no surprise that there are different values to use for boolean fields:
Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
---|---|---|---|---|---|---|
TRUE | '1' | '1' | ✓ | 1 | ✓ | '1' |
FALSE | '0' | '0' | ✓ | 0 | ✓ | '0' |
Transaction control differs slightly among the supported databases:
Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
---|---|---|---|---|---|---|
BEGIN | auto-commit disabled | BEGIN TRANSACTION | ✓ | auto-commit disabled | ✓ | ✓ |
COMMIT | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
ROLLBACK | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
SAVEPOINT savepoint_name | ✓ | SAVE TRANSACTION savepoint_name | ✓ | ✓ | ✓ | ✓ |
ROLLBACK TO SAVEPOINT savepoint_name | ✓ | ROLLBACK TRANSACTION savepoint_name | ✓ | ✓ | ✓ | ✓ |
The following constructs are are used with the data manipulation language (DML) statements in SQL. Such statements include SELECT
, INSERT
, UPDATE
and DELETE
.
Operators are fairly consistent across the different databases, with the exception of the concatenation operator. Please note that ||
works for concatenation in MySQL only when MySQL is in ANSI mode (Flourish automatically switches into ANSI mode when a connection to a MySQL database is initiated).
Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
---|---|---|---|---|---|---|
Mathematical Operators | ||||||
+ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
- | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
/ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
* | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
% | mod() | ✓ | ✓ | mod() | ✓ | ✓ |
String Operators | ||||||
|| (concatenation) | ✓ | + | ✓ | ✓ | ✓ | ✓ |
Comparison Operators | ||||||
< | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
> | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
<= | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
>= | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
<> (inequality) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
!= | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
= | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
IN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
NOT IN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
IS (equality with NULL) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
IS NOT (inequality with NULL) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
LIKE (case insensitive) | lower(value) LIKE lower(pattern) | ✓ | ✓ | lower(value) LIKE lower(pattern) | ILIKE | ✓ |
Boolean Operators | ||||||
AND | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
OR | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
There a quite a few functions that are consistent across the different databases:
Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
---|---|---|---|---|---|---|
Mathematical Functions | ||||||
abs(x) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
acos(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
asin(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
atan(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
atan2(x, y) | ✓ | atn2(x, y) | ✓ | ✓ | ✓ | PHP Callback |
ceil(x) | ceiling(x) | ceiling(x) | ✓ | ✓ | ✓ | PHP Callback |
ceiling(x) | ✓ | ✓ | ✓ | ceil(x) | ✓ | PHP Callback |
cos(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
cot(x) | (1/tan(x)) | ✓ | ✓ | (1/tan(x)) | ✓ | PHP Callback |
degrees(x) | ✓ | ✓ | ✓ | (x * 57.295779513083) | ✓ | PHP Callback |
exp(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
floor(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
ln(x) | ✓ | log(x) | ✓ | ✓ | ✓ | PHP Callback |
log(b, x) | (ln(x)/ln(b)) | (log(x)/log(b)) | ✓ | ✓ | ✓ | PHP Callback |
pi() | 3.14159265358979 | ✓ | (pi()+0.0000000000000) | 3.14159265358979 | ✓ | PHP Callback |
power(x, y) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
radians(x) | ✓ | ✓ | ✓ | (x * 0.017453292519943) | ✓ | PHP Callback |
random() | rand() | rand() | rand() | (abs( dbms_random.random ) / 2147483647) | ✓ | (abs( random()) / 9223372036854775807) |
round(x) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
sign(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
sqrt(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
sin(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
tan(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
Aggregate Functions | ||||||
avg(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
count(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
max(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
min(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
sum(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
String Functions | ||||||
trim(s) | ✓ | rtrim(ltrim(s)) | ✓ | ✓ | ✓ | ✓ |
rtrim(s) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
ltrim(s) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
upper(s) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
lower(s) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
substr(s, start, length) | substring(s, start, length, CODEUNITS32) | substring(s, start, length) | ✓ | ✓ | ✓ | ✓ |
replace(s, find, replace) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
length(s) | character_length(s, CODEUNITS32) | len(s) | char_length(s) | ✓ | ✓ | ✓ |
coalesce(a, b,...) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
Date/Time Functions | ||||||
CURRENT_TIMESTAMP | ✓ | ✓ | ✓ | ✓ | ✓ | datetime( CURRENT_TIMESTAMP, 'localtime') |
A quick scan of the SQLite column indicates that the trigonometric functions are all implemented as PHP callbacks. SQLite made the decision to include only specific functions in the supported SQL dialect, however they added support for hooks into other programming languages. Flourish uses this ability to provide support for trigonometric functions.
There are a few expressions that are consistent across databases:
Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
---|---|---|---|---|---|---|
BETWEEN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
CASE (simple) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
CASE (complex) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
There are quite a few different types of join supported by the four different databases, however the common join functionality is a small subset. Note that all joins except CROSS
and ,
require use of an ON
clause, while CROSS
and ,
can not use an ON
clause.
Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
---|---|---|---|---|---|---|
, (cross join) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
CROSS JOIN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
[INNER] JOIN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
LEFT [OUTER] JOIN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
The brackets indicate the words OUTER
and INNER
are optional.
You may notice the lack of a FULL [OUTER] JOIN
, RIGHT [OUTER] JOIN
, the keyword NATURAL
and USING
clauses. Unfortunately DB2, MySQL and SQLite do not support FULL
joins, SQLite does not support RIGHT
joins and DB2 and MSSQL do not support NATURAL
joins. USING (col, ...)
clauses are not supported by MSSQL.
FULL
joins can be achieved by a somewhat complex combination of a SELECT
from the first table with extra columns UNION
ed with an INNER JOIN
, UNION
ed with a SELECT
from the second table with extra columns.
RIGHT
joins can be performed by switching the order of the tables in the FROM
clause.
NATURAL
joins are just shorthand for the common columns between tables, so all that needs to be done is an INNER
join with manually specified columns in the ON
condition.
USING
clauses can be approximated with a ON
condition, however the USING
operator ensures that only one copy of each column is included in the returned rows, whereas ON
does not.
There are a few clauses that are supported across all databases:
Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
---|---|---|---|---|---|---|
DISTINCT (follows SELECT) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
ALL (follows SELECT) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
LIMIT (without an offset) | FETCH FIRST n ROWS ONLY | TOP | ✓ | subquery with rownum clause | ✓ | ✓ |
LIMIT/OFFSET | subquery with row_number() clause and removal of extra column via PHP | subquery with row_number() clause and removal of extra column via PHP | ✓ | nested subqueries with rownum clauses and removal of extra column via PHP | ✓ | ✓ |
UNION | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
UNION ALL | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
The following are the supported data definition language (DDL) statements in Flourish SQL. Some examples of DDL statements include CREATE TABLE
, ALTER TABLE
and CREATE INDEX
.
While DML statements between the supported databases tend to be fairly consistent, DDL statements tend to be much more varied. For instance, all of the supported databases use different constructs to achieve auto-incrementing primary keys.
The following grammars will show the supported Flourish SQL syntax, but do not cover exactly what is run for each type of database. To see what processing takes place, please see the fSQLSchemaTranslation source code, or call fDatabase::enableDebugging() before executing DDL statements.
The following syntax is supported for CREATE TABLE
statements. By strictly following this syntax, and using the data types previously
defined, CREATE TABLE
statements should work identically across all six supported database systems.
CREATE TABLE table_name (
{
column_name data_type [ DEFAULT default_value ] [ column_constraint [ ... ] ] |
table_constraint
}, ...
)
where column_constraint is:
{
NOT NULL |
NULL |
UNIQUE |
[ AUTOINCREMENT ] PRIMARY KEY |
CHECK ( column_name IN ( value1 [, value2 [, ... ] ] ) ) |
REFERENCES referenced_table ( referenced_column ) [ ON DELETE action ]
}
table_constraint is:
{
UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
CHECK ( column_name IN ( value1 [, value2 [, ... ] ] ) ) |
FOREIGN KEY ( column_name ) REFERENCES referenced_table ( referenced_column )
[ ON DELETE action ]
}
and action is:
{ RESTRICT | CASCADE | SET NULL | NO ACTION }
The ALTER TABLE
statements are some of the most powerful aspects of Flourish SQL since the native support for ALTER TABLE
varies wildly between databases. In addition to varying syntax, many databases require explicit constraint names for dropping UNIQUE
, CHECK
, FOREIGN KEY
and PRIMARY KEY
constraints. These constraint names are usually generated by the system and are not consistent across different databases.
To solve these problems, fSQLSchemaTranslation introspects the database and alters the SQL statements to work with the different database engines. In some cases multiple SQL statements must be executed. fSQLSchemaTranslation is written in such a way that each of the statements listen below should be individually atomic. Thus, if the statement fails, the database should remain at the state before the statement was executed.
PostgreSQL, SQLite and MSSQL all fully support transactions for DDL statements, which makes this easy. For DB2, most DDL statements run within a transaction, and the one that doesn't (REORG TABLE
) is only executed once the primary statement succeeds. For Oracle, all of these statement correspond to a single SQL statement, so the statement will either succeed or fail. MySQL requires storing rollback statements for all database operations, and such statements are run if a statement fails. The tests for Flourish include a bunch of tests to ensure that statements are run atomically.
The syntax is strongly based on PostgreSQL's ALTER TABLE
statements, since they are powerful and succinct. Users who are more familiar with other database systems, especially SQLite and MySQL, will hopefully find these statement significantly easier to use than native commands.
Of special note among the supported database systems is SQLite, since it only natively supports renaming tables and adding columns for version 3.x and nothing for version 2.x. Flourish does a fairly significant amount of work for SQLite, which includes:
Some of these steps require significant sub-steps, such as in SQLite 2, where table renaming does not exists. Luckily SQLite allows DDL statements within transactions, so everything is safe, but ALTER TABLE
statements may run more slowly because of the extensive amount of work required.
ALTER TABLE table_name RENAME TO new_table_name
An fSQLException will be thrown if a table with the new name already exists.
ALTER TABLE table_name ADD COLUMN column_name data_type [ DEFAULT default_value ] [ column_constraint [ ... ] ]
The data_type
, default_value
and column_constraint
values are all exactly the same as those for Create Table statements. An fSQLException will be thrown if a column with the same name already exists or there is an error in the column definition.
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name
An fSQLException will be thrown if a column with the new name already exists.
ALTER TABLE table_name DROP COLUMN column_name
Any UNIQUE
, FOREIGN KEY
or PRIMARY KEY
constraints that involve this column will also be dropped. An fSQLException will be thrown if the column does not exist.
ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type
For many of the supported databases, there are restrictions about what data types can be automatically converted. In general it is safe to change the data type to increase the size, such as making a VARCHAR
longer, or changing from an INTEGER
to a BIGINT
.
For more drastic data type changes, it is normally necessary to:
CAST()
statementUNIQUE
, FOREIGN KEY
and PRIMARY KEY
constraints that existed on the old columnALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value
Sets the default value for a column, overriding any previous value. Some databases only support constant values for default_value
. For example, not all databases allow setting CURRENT_TIMESTAMP()
to be a default value since it is a function.
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT
Drops the default value for a column. This statement will succeed even if no default value exists.
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL [ DEFAULT default_value ]
Sets a column to not allow NULL
values, and optionally sets the default value for a column. This statement will succeed even if the column already restricts NULL
values.
This combined form of SET NOT NULL
and SET DEFAULT
is useful to support NOT NULL DEFAULT ''
configurations on Oracle databases, where blank strings are automatically converted to NULL
. NOT NULL DEFAULT ''
is useful for writing simpler SQL statements since it is no longer necessary to specially test for NULL
values via (column_name IS NULL OR column_name = '')
.
ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL
Changes a column to allow NULL
values. For most databases this will not succeed on columns that are part of a PRIMARY KEY
constraint. This statement will succeed even if the column already allows NULL
s.
ALTER TABLE table_name ALTER COLUMN column_name SET CHECK IN (string_value [, ... ] )
Creates a CHECK(column_name IN ('string value', 'string value'))
check constraint for the column. This check constraint will override any existing check constraint.
MySQL doesn't support CHECK
constraints, but the ENUM()
data type is basically a check constraint for a set of string values. Thus for MySQL, this statement will turn a VARCHAR
column into an ENUM()
column with the strings provided.
ALTER TABLE table_name ALTER COLUMN column_name DROP CHECK
Drop the CHECK
constraint for a column. An fSQLException will be thrown if no check constraint exists.
MySQL doesn't support CHECK
constraints, but the ENUM()
data type is basically a check constraint for a set of string values. Thus the only cross- database compatible CHECK
constraint is CHECK(column_name IN ('string value', 'string value 2'))
, which will be automatically converted to an ENUM()
for MySQL. Consequently, when DROP CHECK
is called for MySQL, an ENUM()
column will be coverted to a VARCHAR
.
ALTER TABLE table_name ADD PRIMARY KEY (column_name [, ... ] ) [ AUTOINCREMENT ]
Creates a primary key for table_name
with the columns specified. An fSQLException will be thrown if a primary key already exists.
If only one column is specified and the AUTOINCREMENT
keyword is provided, the column will be configured to automatically created auto-incrementing integer values when no value is provided. The actual implementation of AUTOINCREMENT
varies widely between databases. Please see Data Types for details.
ALTER TABLE table_name DROP PRIMARY KEY
Drops the primary key for table_name
. Any foreign keys that reference this primary key will also be dropped. An fSQLException will be thrown if a primary key does not exist.
ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES foreign_table(foreign_column) [ ON DELETE action ]
Adds a foreign key to the column specified. Currently, Flourish as a whole, only supports single-column foreign key constraints. This restriction is also present for this ALTER TABLE
statement. The valid action
values are the same as those supported by Create Table. An fSQLException will be thrown if a foreign key already exist.
Since DB2 and Oracle do not fully support ON UPDATE
clauses, such a clause will be automatically removed for those databases.
ALTER TABLE table_name DROP FOREIGN KEY (column_name)
Drops the foreign key for the column specified. Currently, Flourish as a whole, only supports single-column foreign key constraints. An fSQLException will be thrown if a foreign key does not exist.
ALTER TABLE table_name ADD UNIQUE (column_name [, ... ] )
Adds a unique constraint to the column(s) specified. Some databases, such as MSSQL and DB2, treat a NULL
value as a distinct value, only allowing one per column. PostgreSQL, SQLite, Oracle and MySQL treat NULL
specially and allow any number of NULL
s in a unique column.
ALTER TABLE table_name DROP UNIQUE (column_name [, ... ] )
Drop the unique constraint that exists for the column(s) specified. Any foreign keys that reference this unique constraint will also be dropped. An fSQLException will be thrown if a UNIQUE
constraint does not exist for the column(s) specified.
COMMENT ON COLUMN table_name.column_name IS 'Comment value'
For SQLite, this statement adds an inline SQL comment at the end of the line that defines the column. All other databases have a native system for storing comments.
DROP TABLE table_name
The following syntax is supported for CREATE INDEX
statements.
CREATE [ UNIQUE ] INDEX index_name ON table_name ( column_name [, ... ] )
DROP INDEX index_name
All of the databases supported by Flourish support foreign key constraints through some method. By using translatedQuery()
to execute CREATE TABLE
statements, you can be sure that foreign keys will be enforced.
PostgreSQL, MSSQL, Oracle and DB2 support foreign keys completely natively. MySQL supports them natively as long as the InnoDB engine type is specified in the CREATE TABLE
statement. This is automatically added when using translatedQuery()
.
SQLite supports the syntax, however enforcement before version 3.6.19 has to be done through triggers. translatedQuery()
will create the necessary triggers to enforce foreign key constraints on an SQLite database.
Please note that Oracle does not support ON UPDATE
clauses, and DB2 only supports the NO ACTION
and RESTRICT
actions for ON UPDATE
clauses. Because of these limitations, it is best to avoid ON UPDATE
clauses for cross-database applications.
In addition, Flourish is designed to support only single-column foreign keys. While most databases support multi-column foreign keys, Flourish's SQLite foreign key trigger generation only supports a single column. In addition, the ALTER TABLE
through Flourish SQL is only designed and tested against single- column foreign keys.