Why do I get 'DBD::SQLite::db do failed: near "transaction": syntax error' when I try to create a SQLite table with a foreign key constraint?

Question

I'm trying to create a database with two tables: customer and transaction (one customer can do many transactions, one transaction can only be done by one customer). I'm using the following Perl code:

my $dbh = DBI->connect("dbi:SQLite:dbname=$RealBin/active.db","","")
    or die $DBI::errstr;

my @ddl = (
    "CREATE TABLE IF NOT EXISTS customer (
      id INTEGER PRIMARY KEY UNIQUE,
      id_1 INTEGER,
      id_2 INTEGER,
      name TEXT
    )",
    "CREATE TABLE IF NOT EXISTS transaction (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      customer_id INTEGER REFERENCES customer(id),
    )"
);

for my $sql (@ddl){
  $dbh->do($sql);
}

When I run my script, I'm getting the following error:

DBD::SQLite::db do failed: near "transaction": syntax error at
    t/oneToMany.t line 28 (#1)
DBD::SQLite::db do failed: near "transaction": syntax error at t/oneToMany.t line 28.
DBD::SQLite::db do failed: near "transaction": syntax error at
    t/oneToMany.t line 38 (#1)

The line number 28 is this one:

$dbh->do($sql);

Can you please tell me what I'm doing wrong?

Answer 1

transaction is a SQLite keyword, so you have to quote it if you want to use it as an identifier (although it's probably better to just pick a different name for your table).

Your foreign key constraint is also a little off. You need to specify a column definition for customer_id in addition to the constraint, and begin the constraint definition with FOREIGN KEY.

Your query should look like this:

CREATE TABLE IF NOT EXISTS "transaction" (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customer(id)
)

Also, foreign key constraints are only enforced if you explicitly enable them:

SQLite has long been able to parse a schema with foreign keys, but the constraints has not been enforced. Now you can issue a foreign_keys pragma to enable this feature and enforce the constraints, preferably as soon as you connect to a database and you're not in a transaction:

$dbh->do("PRAGMA foreign_keys = ON");

And you can explicitly disable the feature whenever you like by turning the pragma off:

$dbh->do("PRAGMA foreign_keys = OFF");

As of this writing, this feature is disabled by default by the SQLite team, and by us, to secure backward compatibility, as this feature may break your applications, and actually broke some for us. If you have used a schema with foreign key constraints but haven't cared them much and supposed they're always ignored for SQLite, be prepared, and please do extensive testing to ensure that your applications will continue to work when the foreign keys support is enabled by default.

All together:

use strict;
use warnings 'all';

use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=foo.sqlite', '', '', {
    RaiseError => 1,
    PrintError => 0,
});

$dbh->do('PRAGMA foreign_keys = ON');

my @ddl = (
    'CREATE TABLE IF NOT EXISTS customer (
        id INTEGER PRIMARY KEY UNIQUE,
        id_1 INTEGER,
        id_2 INTEGER,
        name TEXT
    )',
    'CREATE TABLE IF NOT EXISTS "transaction" (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        customer_id INTEGER,
        FOREIGN KEY (customer_id) REFERENCES customer(id)
    )'
);

for my $sql (@ddl) {
    $dbh->do($sql);
}