SQLite UUID as column default

Question

I am trying to reproduce the SQL Server default NEWID() value for a UNIQUEIDENTIFIER column in SQLite. It produces a UUID as a default for the column.

How can I do that in SQLite?

I found this page (Is there UID datatype in SQLITE if Yes then how to generate value for that) where they use a select statement but it is no good for DEFAULT.

NOTE : I am using the SQLite version implemented for browsers as I am using it through ionic and the cordova-sqlite plugin. So this does not contain all the features provided by SQLite and more importantly this does not contain the RANDOM function.

AUTO INCREMENT is not sufficient? – juergen d Sep 24 '16 at 20:26 — juergen d, Sep 24 '16 at 20:26
AUTOINCREMENT does nothing with UNIQUEIDENTIFIER type. – Linvi Sep 24 '16 at 20:48 — Linvi, Sep 24 '16 at 20:48

score 2 · Answer 1 · answered Sep 25 '16 at 18:48

Yes, you can use create function to add a UUID function. By default you'd have to write it in C, but many SQLite drivers have a way to add functions in their native language. Here's an example in Perl using DBD::SQLite.

use strict;
use warnings;
use v5.10;

use DBI;
use Data::UUID;

# Connect to the database.
my $dbh = DBI->connect("dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1});

# Create uuid() which calls Perl to generate a uuid.
$dbh->sqlite_create_function("uuid", 0, sub { Data::UUID->new->create_str });

# Create a table using uuid() as the default.
$dbh->do(q[drop table if exists foo]);
$dbh->do(q[
    create table foo (
        id int primary key default (uuid()),
        name text
    )
]);

# Insert some rows using the default id.
$dbh->do(q[
    insert into foo (name) values ("you"), ("me")
]);

# Print out the rows.
my $rows = $dbh->selectall_arrayref(q[select * from foo]);
for my $row (@$rows) {
    say "$row->[0], $row->[1]";
}

And the result is.

E9768488-834F-11E6-AA60-F143DC5749B0, you
E976B69C-834F-11E6-AA60-F143DC5749B0, me

SQLite UUID as column default

1 Answers1