On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof <cldwesterhof at gmail.com> wrote:
> I want to work with UUID's. (Version 4.) Honestly, your post is a little vague. But maybe the following will help. > What is the smartest way to put a check on this? > check what? CHECK constraint? SQLite is dynamically typed, and to enforce a column's type you can use check constraints. A blob UUID is 16 bytes, not 4. So 36 vs 16 is a matter of choice. I prefer blob guids myself. See below for uid-related insert/select SQL. --DD PS: Note that these are random blobs, so the type-bits don't follow the UUID spec. I don't care personally. YMMV. Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:\Users\DDevienne>sqlite3 SQLite version 3.10.2 2016-01-20 15:27:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> sqlite> create table t (uid blob primary key); sqlite> insert into t values (1), ('foo'), (x'abcd'); sqlite> select uid, typeof(uid), length(uid) from t; 1|integer|1 foo|text|3 ??|blob|2 sqlite> create table t (uid blob primary key CHECK(typeof(uid)='blob' and length(uid)=16)); sqlite> select uid, typeof(uid), length(uid) from t; sqlite> insert into t values (1), ('foo'), (x'abcd'); Error: CHECK constraint failed: t sqlite> select uid, typeof(uid), length(uid) from t; sqlite> insert into t values (randomblob(16)); sqlite> insert into t values (randomblob(16)); sqlite> insert into t values (randomblob(16)); sqlite> select * from t; ?2?!??? ????o??????T?& e?rBq.???H??f? sqlite> select quote(uid) from t; X'674ED1D53CABCA0D86329A219F0EE8A4' X'11F4BBE18CF36FC2C8159D9CEB54F126' X'65F37242712E89A2E894480107F466A6' sqlite> select hex(uid) from t; 674ED1D53CABCA0D86329A219F0EE8A4 11F4BBE18CF36FC2C8159D9CEB54F126 65F37242712E89A2E894480107F466A6 sqlite> select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)|| ...> '-'||substr(u,17,3)||'-'||substr(u,21,12) from ( ...> select hex(uid) as u from t ...> ); 674ED1D5-3CAB-4CA0-863-9A219F0EE8A4 11F4BBE1-8CF3-46FC-C81-9D9CEB54F126 65F37242-712E-489A-E89-480107F466A6 sqlite>